XQUERY using Cross Apply and Outer Apply

trying shred xml relational table rows , can 1 set of node values .. having difficulty creating pair of columns. relevant part of xml looks ...

<ns:coverage>

     <ns:coveragecd>cvalue1</ns:coveragecd>

     <ns:form>

          <ns:formnumber>fvalue1</ns:formnumber>

     </ns:form>

</ns:coverage>

i can navigate through xml , pull set of rows values of coveragecd. i'm trying add column each output row formnumber value associated coveragecd value. however; formnumber not present. found example using 'outer apply' can account that, it's not working me.

here's xquery:

with xmlnamespaces('http://www.mycompanyname.com/acord1.11.0/policy_1.0/xml' "ns")

select cov.i.value('.', 'varchar(30)')   [coverage],
         form.i.value('.', 'varchar(30)')  [formnumber]

from  dbo.mytablename
       cross apply xmlcolumn.nodes('.//ns:coverage/ns:coveragecd') cov(i)
       outer apply cov.i.nodes('.//ns:coverage/ns:form/ns:formnumber) form(i)

with cross apply, can output of coveragecd values ... adding outer apply, second column, values blank/null.

think i'm missing obvious ... in advance help.

dave

try this:

  with xmlnamespaces('http://www.mycompanyname.com/acord1.11.0/policy_1.0/xml' as "ns")  select   	covcd.i.value('.', 'varchar(30)')  [coverage],    form.i.value('.', 'varchar(30)') [formnumber]  from dbo.mytablename      cross apply xmlcolumn.nodes('//ns:coverage') as cov(i)      outer apply cov.i.nodes('ns:coveragecd') as covcd(i)      outer apply cov.i.nodes('ns:form/ns:formnumber') as form(i)  go  

outer / cross apply drill xml address (xpath) give them , cross-applying with.  second outer apply kind of says:

ns:coverage/ns:coveragecd/ns:coverage/ns:form/ns:formnumber

does make sense?  tend use different cross apply statements drill down different paths in xml. 

SQL Server  >  SQL Server XML