CODEWITHSUNDEEP
sql-serverxml
VinnyGuitara
VinnyGuitara

Reputation: 605

Access multiple nodes of XML document for SQL Server

I have an XML doc like this:

DROP TABLE #temp
create table #temp (xml_data xml)

insert into #temp (xml_data) values
('<UPDATE><ENTITY DN="test"><ATTRIBUTE N="test name" V="test value" />     </ENTITY></UPDATE>')

select C.value('@DN', 'varchar(max)') as [ENTITY.DN]
      ,C.value('@N', 'varchar(max)') as [ATTRIBUTE.N]
      ,C.value('@V', 'varchar(max)') as [ATTRIBUTE.V]
from #temp cross apply
     #temp.xml_data.nodes('UPDATE/ENTITY') as X(C)

My output ends up like this:

test | NULL | NULL

I'd like to see:

test | test name | test value

Any thoughts on how I've configured this incorrectly?

Upvotes: 0

Views: 213

Answers (3)

har07
har07

Reputation: 89285

In case one ENTITY might have multiple ATTRIBUTE child -based on your comment-, you'll need another APPLY to shred on ATTRIBUTE, for example :

declare @temp table(xml_data xml)
insert into @temp (xml_data) values
('<UPDATE>
  <ENTITY DN="test">
    <ATTRIBUTE N="test name" V="test value"/> 
    <ATTRIBUTE N="foo1" V="bar1"/>
    <ATTRIBUTE N="foo2" V="bar2"/>
  </ENTITY>
</UPDATE>')

select ent.value('@DN', 'varchar(max)') as [ENTITY.DN]
      ,attr.value('@N', 'varchar(max)') as [ATTRIBUTE.N]
      ,attr.value('@V', 'varchar(max)') as [ATTRIBUTE.V]
from @temp t 
     cross apply t.xml_data.nodes('UPDATE/ENTITY') as X(ent)
     cross apply ent.nodes('./ATTRIBUTE') as Y(attr)

SQLFiddle Demo

output :

| ENTITY.DN | ATTRIBUTE.N | ATTRIBUTE.V |
|-----------|-------------|-------------|
|      test |   test name |  test value |
|      test |        foo1 |        bar1 |
|      test |        foo2 |        bar2 |

Upvotes: 2

S. Rojak
S. Rojak

Reputation: 454

The answer DimaSUN provided will work. Here is an alternative query:

SELECT xml_data.query('data(/UPDATE/ENTITY/@DN)') as [ENTITY.DN],
        xml_data.query('data(/UPDATE/ENTITY/ATTRIBUTE/@N)') as [ATTRIBUTE.N],
        xml_data.query('data(/UPDATE/ENTITY/ATTRIBUTE/@V)') as [ATTRIBUTE.V]
    FROM #temp;

Upvotes: 0

DimaSUN
DimaSUN

Reputation: 921

change to this

DROP TABLE #temp
create table #temp (xml_data xml)

insert into #temp (xml_data) values
('<UPDATE><ENTITY DN="test"><ATTRIBUTE N="test name" V="test value" />     </ENTITY></UPDATE>')

select C.value('@DN', 'varchar(max)') as [ENTITY.DN]
      ,C.value('ATTRIBUTE[1]/@N', 'varchar(max)') as [ATTRIBUTE.N]
      ,C.value('ATTRIBUTE[1]/@V', 'varchar(max)') as [ATTRIBUTE.V]
from #temp cross apply
     #temp.xml_data.nodes('UPDATE/ENTITY') as X(C)

Upvotes: 0

Related Questions