Navigating nodes for select in XML (Crossapply,XMLnamespaces) SQL

44 views Asked by At

I have a query that will bring me back correct nodes from XML. However when I try to change the criteria I get no results

I know Im probably just referencing the wrong nodes but this is a one off and I have no real experience in XML/nodes and got the script online and changed minimally for it to work.

Can you help?

I an get the TAB values OK but when I try to change the TNS: for section nodes it returns no results - I just need the section caption and ID.

XML:

 </Tab>
<Tab Caption="Works" ID="7f789fee-1aa4-4341-801a-f31d1daf1bcc" 
  <Tabs>
    <Tab Caption="Works" ID="24e52dcf-fb35-4a29-8890-9eec6bde28c2" 
      <Sections>
        <Section ID="631e1555-89fa-4306-801f-6a1d7b23a435" Caption="ThisOne" 
        </Section>
      </Sections>
    </Tab>

Script

    with xmlnamespaces ('Page' as tns, 'commontypes' as common)

select 
    page.section.value('@ID', 'uniqueidentifier') as ID_GUID,
    page.section.value('@Caption', 'nvarchar(max)') as Section
from dbo.PAGELIBRARY as P
  cross apply P.PAGELIBRARYXML.nodes('tns:Page/tns:Sections/tns:Section') as page(section) 
where  P.ID = '88159265-2b7e-4c7b-82a2-119d01ecd40f' 

I can retrieve the TAB info OK using

 cross apply P.PAGELIBRARYXML.nodes('tns:Page/tns:Tabs/tns:Tab') as page(tab)
1

There are 1 answers

0
davie On

Specifying the below example allowed me to navigate through the xml element nodes - tns:root/tns:/firstNode/tns:/childnode etc etc

P.PAGEDEFINITIONSPEC.nodes('tns:PageDefinitionSpec/tns:Tabs/tns:Tab/tns:Sections/tns:Section')

Thanks to Yitzhak Khabinsky for guidance