How do I find all InsurerNames with values other than NULL using query to a ntext field and cross applies?
The code below returns all NULL values. I am currently setting InsName to ' ' (Incorrectly) because I don't know how to get it to retrieve everything but the rows with a null value for InsurerName.
DECLARE @InsName varchar(max)
SET @InsName = ''
SELECT
InsurerName.value('/data[1]/ACORD[1]/InsuranceSvcRq[1]/HomePolicyAddRq[1]/PersPolicy[1]/OtherOrPriorPolicy[1]', 'varchar(max)') as InsurerName
,LOB
,PolicyNumber
,DateModified
FROM Quote
CROSS APPLY (SELECT CAST(XMLData AS XML)) AS A(B)
--CROSS APPLY A.B.nodes('//session/data/ACORD/InsuranceSvcRq/HomePolicyAddRq/PersPolicy/OtherOrPriorPolicy[InsurerName != sql:variable("@InsName")]') AS InsurerNames(InsurerName)
CROSS APPLY A.B.nodes('//session/data/ACORD/InsuranceSvcRq/HomePolicyAddRq/PersPolicy/OtherOrPriorPolicy[InsurerName != sql:variable("@InsName")]') AS InsurerNames(InsurerName)
WHERE LOB = 'HO'
AND
DateModified > 2014/10/1
ORDER BY DateModified DESC
No comments:
Post a Comment