Access multiple nodes of XML document for SQL Server

Question

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?

I think I answered my own question. Would this be the correct way to do what I want? 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] ,r.value('@N', 'varchar(max)') as [ATTRIBUTE.N] ,r.value('@V', 'varchar(max)') as [ATTRIBUTE.V] from #temp cross apply #temp.xml_data.nodes('UPDATE/ENTITY') as X(C) cross apply #temp.xml_data.nodes('UPDATE/ENTITY/ATTRIBUTE') as Y(r) — VinnyGuitara
– VinnyGuitara, Commented Jan 6, 2016 at 22:55
insert into #temp (xml_data) values ('<UPDATE><ENTITY DN="test"><ATTRIBUTE N="test name" V="test value" /> </ENTITY> <ENTITY DN="test1"><ATTRIBUTE N="test name1" V="test value1" /> </ENTITY> </UPDATE>') — DimaSUN
– DimaSUN, Commented Jan 6, 2016 at 23:11

har07 · Accepted Answer · 2016-01-08 01:25:34Z

2

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 |

answered Jan 8, 2016 at 1:25

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VinnyGuitara Over a year ago

Thanks to all but this is the answer that satisfied my issue. Appreciated.

DimaSUN · Accepted Answer · 2016-01-06 22:55:27Z

0

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)

answered Jan 6, 2016 at 22:55

DimaSUN

9218 silver badges13 bronze badges

2 Comments

marc_s Over a year ago

And your code is going to break if there is ever a <ENTITY> node with more than one <ATTRIBUTE> subnode .....

VinnyGuitara Over a year ago

What if there were more attributes inside the entity tag @DimaSum? insert into #temp (xml_data) values ('<UPDATE><ENTITY DN="test"><ATTRIBUTE N="test name" V="test value" /><ATTRIBUTE N="test2 name" V="test2 val" /></ENTITY></UPDATE>')

S. Rojak · Accepted Answer · 2016-01-06 22:59:21Z

0

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;

answered Jan 6, 2016 at 22:59

S. Rojak

4542 silver badges6 bronze badges

2 Comments

DimaSUN Over a year ago

Check u'rs answer against 2..N entities. U'll get wrong result... :(

DimaSUN Over a year ago

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

Collectives™ on Stack Overflow

Access multiple nodes of XML document for SQL Server

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related