I am new to SQL Server and T-SQL. How can I query out the information stored in this xml in SQL Server 2008 R2?
XML :
<smp:Root xmlns:smp="http://tempuri.org/smp.xsd" header="Test Title">
<smp:Sections>
<smp:G3 idnumber="01">
<SectionHost>ABC</SectionHost>
</smp:G3>
<smp:G2 idnumber="01">
<SectionHost>DEF</SectionHost>
</smp:G2>
</smp:Sections>
</smp:Root>
If you have the Xml stored in a Xml column just use the value method to shred the Xml.
Next time try and post some DDL, DML to show us what you have tried, your table structures etc.
But try this
WITH XMLNAMESPACES (Default 'http://tempuri.org/smp.xsd')
SELECT
a.value('@header', 'nvarchar(50)') as Header,
b.value('local-name(.)', 'nvarchar(50)') as Sections,
b.value('@idnumber' ,'int') as IdNumber,
b.value('.' , 'nvarchar(20)') as Host
From ATable As x
Cross Apply x.AXmlColumn.nodes('Root') a(a)
Cross Apply a.nodes('Sections/*') b(b)
Here are some useful links to get you started:
https://www.simple-talk.com/sql/learn-sql-server/the-xml-methods-in-sql-server/
