Query ntext field containing xml using cross apply?



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