How to get the name of XML elements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the name of XML elements

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-10-05 : 04:51:56

Hi All,

Hi All
I have a table with a xml data type, and I need to get each element name of the xml. I don't always know what XML elements will be present, but I need to place all of them in a SQL Server 2008 database.

Every row of the table may have different xml structure.

For example, i have the following xml:

<MessageDelivery version="B000">
<ReturnMessage id="3652789340">
<AdC ocean="PACCGL">4815044</AdC>
<MessageStatus code="100" time="2011-09-25 13:43:22">status ok</MessageStatus>
<MessageData>700A20000018C0375494400000</MessageData>
<Flags les="0" app="0" read="1" />
</ReturnMessage>
</MessageDelivery>

and I would like to place them into a table like this:

Element Value
---------- --------------------
AdC PACCGL
AdC 4815044
MessageStatus status ok
MessageData 700A20000018C0375494400000

I´ll apreciate your help

rams

Ehan
Starting Member

19 Posts

Posted - 2011-10-05 : 05:14:36

declare @xml xml,@idoc int
set @xml ='<MessageDelivery version="B000">
<ReturnMessage id="3652789340">
<AdC ocean="PACCGL">4815044</AdC>
<MessageStatus code="100" time="2011-09-25 13:43:22">status ok</MessageStatus>
<MessageData>700A20000018C0375494400000</MessageData>
<Flags les="0" app="0" read="1" />
</ReturnMessage>
</MessageDelivery>'

exec sp_xml_preparedocument @idoc output,@xml
select e.localname as element,v.text as value from
openxml(@idoc,'MessageDelivery',2) e
join (select * from
openxml(@idoc,'MessageDelivery',1)
where nodetype = 3) v
on v.parentid = e.id
where e.nodetype = 1

exec sp_xml_removedocument @idoc

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-10-05 : 05:20:58

Hi Ehan,

Thanks a lot! That worked for me exactly :)

rams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 05:37:40

also


select t.c.value('local-name(.)','varchar(100)'),
       t.c.value('data(.)','varchar(100)'),
       u.v.value('local-name(.)','varchar(100)'),
       u.v.value('data(.)','varchar(100)')
from @x.nodes('MessageDelivery/ReturnMessage/descendant::*')t(c)
outer apply t.c.nodes('attribute::*')u(v)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-10-05 : 05:51:01

Thanks a lot guys

rams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 06:00:57

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources