Trying to shred XML into relational table rows and can do it for one set of node values .. having difficulty creating a pair of columns. The relevant part of the XML looks like this ...
I can successfully navigate through the XML and pull back a set of rows for all values of CoverageCd. Now I'm trying to add a column to each output row for the FormNumber value associated with the CoverageCd value. However; FormNumber is not always
present. I found an example where using 'Outer Apply' can account for that, but it's not working for me.
Here's my XQuery:
WITH XMLNAMESPACES('http://www.mycompanyname.com/ACORD1.11.0/Policy_1.0/xml' AS "ns")
select cov.i.value('.', 'varchar(30)') [Coverage],
form.i.value('.', 'varchar(30)') [FormNumber]
cross apply XMLcolumn.nodes('.//ns:Coverage/ns:CoverageCd') as cov(i)
outer apply cov.i.nodes('.//ns:Coverage/ns:Form/ns:FormNumber) as form(
View Complete Post