CODEWITHSUNDEEP
sqlxmlt-sql
Janusz Jasinski
Janusz Jasinski

Reputation: 15

xQuery and T-SQL to Extract Data

Got some basic XML as a XML datatype within SQL 2005. One record/row looks like this

<doc>
  <level1>

    <level2>
      <name>James</name>
      <age>12</age>
    </level2>

    <level2>
      <name>John</name>
      <age>23</age>
    </level2>

  </level1>
</doc>

When I perform some basic T_SQL

SELECT TOP 1 
DocumentXML.query('data(//doc/name)'),
DocumentXML.query('data(//doc/age)')
FROM [DBNAME].[dbo].[TBLNAME]

I get

ID  |  Name     | Age
----------------------
1   | JamesJohn | 1223

How do I re-write the T-SQL so it displays as

ID  |  Name  |  Age
--------------------
1   |  James |  12
2   |  John  |  23

Upvotes: 1

Views: 1568

Answers (1)

Andomar
Andomar

Reputation: 238296

Your example doesn't work for me; the second level2 opens with </level2>. And //doc/name doesn't exist; might be //doc/level1/level2/name.

Here's an example of how to retrieve a rowset from an XML:

declare @t table (id int identity, doc xml)
insert @t (doc) values (
    '<doc>
      <level1>

        <level2>
          <name>James</name>
          <age>12</age>
        </level2>

        <level2>
          <name>John</name>
          <age>23</age>
        </level2>

      </level1>
    </doc>')

SELECT  x.a.value('(name)[1]','varchar(50)') as col1
,       x.a.value('(age)[1]','varchar(50)') as col2
FROM    @t t
cross apply
        t.doc.nodes('//level2') x(a)

This prints:

col1     col2
James    12
John     23

Upvotes: 5

Related Questions