3

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>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LionsFan
  • 35
  • 4

1 Answers1

3

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/

http://blog.sqlauthority.com/2008/01/15/sql-server-what-is-dml-ddl-dcl-and-tcl-introduction-and-examples/

http://msdn.microsoft.com/en-us/library/ms189254.aspx

KeyboardFriendly
  • 1,798
  • 5
  • 32
  • 42