retrieve the Name Of XML elements in a table

Question

Post reply

retrieve the Name Of XML elements in a table

rams.prsk

Ten Centuries

Points: 1241
More actions
October 5, 2011 at 2:52 am

#74676

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 1

See if this helps

DECLARE @x XML

SET @x='<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>

'

SELECT x.r.value('local-name(.)','VARCHAR(20)') AS Element,

x.r.value('./text()[1]','VARCHAR(20)') AS ElementValue,

a.b.value('local-name(.)','VARCHAR(20)') AS Attribute,

a.b.value('.','VARCHAR(20)') AS AttributeValue

FROM @x.nodes('//*') AS x(r)

OUTER APPLY x.r.nodes('@*') AS a(b);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

rams.prsk Ten Centuries Points: 1241 More actions · Answer 2

rams.prsk

Ten Centuries

Points: 1241

October 5, 2011 at 3:16 am

#1390548

Thanks a lot 🙂

It worked for me exactly

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Mark-101232 (10/5/2011)
See if this helps
DECLARE @x XML
SET @x='<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>
'
SELECT x.r.value('local-name(.)','VARCHAR(20)') AS Element,
x.r.value('./text()[1]','VARCHAR(20)') AS ElementValue,
a.b.value('local-name(.)','VARCHAR(20)') AS Attribute,
a.b.value('.','VARCHAR(20)') AS AttributeValue
FROM @x.nodes('//*') AS x(r)
OUTER APPLY x.r.nodes('@*') AS a(b);

I'm currently doing something very similar and found this post extremely useful.

How would you then take the output and put it back into the XML format?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 4

Cadavre (11/14/2011)
Mark-101232 (10/5/2011)
See if this helps
DECLARE @x XML
SET @x='<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>
'
SELECT x.r.value('local-name(.)','VARCHAR(20)') AS Element,
x.r.value('./text()[1]','VARCHAR(20)') AS ElementValue,
a.b.value('local-name(.)','VARCHAR(20)') AS Attribute,
a.b.value('.','VARCHAR(20)') AS AttributeValue
FROM @x.nodes('//*') AS x(r)
OUTER APPLY x.r.nodes('@*') AS a(b);
I'm currently doing something very similar and found this post extremely useful.
How would you then take the output and put it back into the XML format?

What XML are you trying to generate?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Mark-101232 (11/14/2011)
What XML are you trying to generate?

Let me start again, because I'm sure that I'm attempting to over-complicate this with my lack of XML knowledge.

Take this XML: -

<Message>

<MessageSentCollection>

<MessageSent ID="FB39C47F-B714-48CF-BA86-1D6805394D36">

<MessageName>RE72 3NS</MessageName>

</MessageSent>

<MessageSent ID="41111363-8726-4512-A850-F44277F1FF8F">

<MessageName>RL43 1SY</MessageName>

</MessageSent>

<MessageSent ID="13F0A6C9-5BA9-493B-AAD0-136EF9457902">

<MessageName>BV78 9DC</MessageName>

</MessageSent>

</MessageSentCollection>

<MessageReceivedCollection>

<MessageReceived ID="829DB73E-4480-4A58-95C6-6DB90D9183B4">

<MessageName>BA14 7GR</MessageName>

<MessageSentID>FB39C47F-B714-48CF-BA86-1D6805394D36</MessageSentID>

</MessageReceived>

<MessageReceived ID="BFCD1E81-E20F-41B3-99ED-77F739ACD1BD">

<MessageName>MC83 3EF</MessageName>

<MessageSentID>41111363-8726-4512-A850-F44277F1FF8F</MessageSentID>

</MessageReceived>

<MessageReceived ID="CE55ECBB-B9AF-4168-805C-BD63E7588453">

<MessageName>ZG67 5ZP</MessageName>

<MessageSentID>13F0A6C9-5BA9-493B-AAD0-136EF9457902</MessageSentID>

</MessageReceived>

</MessageReceivedCollection>

</Message>

I now have a new MessageSent and corresponding MessageReceived that have to be added to the XML in the correct positions (under MessageSentCollection and MessageReceivedCollection respectively), e.g.

<MessageSent ID="F11E4BAD-6405-49A6-A453-6EE77F760EBE">

<MessageName>CC83 2OK</MessageName>

</MessageSent>)

<MessageReceived ID="4E4D3B24-C05A-4CF4-A8FB-C7E904E935A0">

<MessageName>SU86 8BE</MessageName>

<MessageSentID>F11E4BAD-6405-49A6-A453-6EE77F760EBE</MessageSentID>

</MessageReceived>

The way I was looking at, was shredding the XML into a table, adding the new data then recreating the XML in the original structure. I've struggled to get the new parts to come into the correct part of the XML, so when I validate against the XSD, it fails.

DECLARE @XML AS XML

SET @XML = CONVERT(XML,'

<Message>

<MessageSentCollection>

<MessageSent ID="FB39C47F-B714-48CF-BA86-1D6805394D36">

<MessageName>RE72 3NS</MessageName>

</MessageSent>

<MessageSent ID="41111363-8726-4512-A850-F44277F1FF8F">

<MessageName>RL43 1SY</MessageName>

</MessageSent>

<MessageSent ID="13F0A6C9-5BA9-493B-AAD0-136EF9457902">

<MessageName>BV78 9DC</MessageName>

</MessageSent>

</MessageSentCollection>

<MessageReceivedCollection>

<MessageReceived ID="829DB73E-4480-4A58-95C6-6DB90D9183B4">

<MessageName>BA14 7GR</MessageName>

<MessageSentID>FB39C47F-B714-48CF-BA86-1D6805394D36</MessageSentID>

</MessageReceived>

<MessageReceived ID="BFCD1E81-E20F-41B3-99ED-77F739ACD1BD">

<MessageName>MC83 3EF</MessageName>

<MessageSentID>41111363-8726-4512-A850-F44277F1FF8F</MessageSentID>

</MessageReceived>

<MessageReceived ID="CE55ECBB-B9AF-4168-805C-BD63E7588453">

<MessageName>ZG67 5ZP</MessageName>

<MessageSentID>13F0A6C9-5BA9-493B-AAD0-136EF9457902</MessageSentID>

</MessageReceived>

</MessageReceivedCollection>

</Message>')

SELECT @XML

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 6

Cadavre (11/14/2011)
Mark-101232 (11/14/2011)
What XML are you trying to generate?
Let me start again, because I'm sure that I'm attempting to over-complicate this with my lack of XML knowledge.
Take this XML: -
<Message>
<MessageSentCollection>
<MessageSent ID="FB39C47F-B714-48CF-BA86-1D6805394D36">
<MessageName>RE72 3NS</MessageName>
</MessageSent>
<MessageSent ID="41111363-8726-4512-A850-F44277F1FF8F">
<MessageName>RL43 1SY</MessageName>
</MessageSent>
<MessageSent ID="13F0A6C9-5BA9-493B-AAD0-136EF9457902">
<MessageName>BV78 9DC</MessageName>
</MessageSent>
</MessageSentCollection>
<MessageReceivedCollection>
<MessageReceived ID="829DB73E-4480-4A58-95C6-6DB90D9183B4">
<MessageName>BA14 7GR</MessageName>
<MessageSentID>FB39C47F-B714-48CF-BA86-1D6805394D36</MessageSentID>
</MessageReceived>
<MessageReceived ID="BFCD1E81-E20F-41B3-99ED-77F739ACD1BD">
<MessageName>MC83 3EF</MessageName>
<MessageSentID>41111363-8726-4512-A850-F44277F1FF8F</MessageSentID>
</MessageReceived>
<MessageReceived ID="CE55ECBB-B9AF-4168-805C-BD63E7588453">
<MessageName>ZG67 5ZP</MessageName>
<MessageSentID>13F0A6C9-5BA9-493B-AAD0-136EF9457902</MessageSentID>
</MessageReceived>
</MessageReceivedCollection>
</Message>
I now have a new MessageSent and corresponding MessageReceived that have to be added to the XML in the correct positions (under MessageSentCollection and MessageReceivedCollection respectively), e.g.
<MessageSent ID="F11E4BAD-6405-49A6-A453-6EE77F760EBE">
<MessageName>CC83 2OK</MessageName>
</MessageSent>)
<MessageReceived ID="4E4D3B24-C05A-4CF4-A8FB-C7E904E935A0">
<MessageName>SU86 8BE</MessageName>
<MessageSentID>F11E4BAD-6405-49A6-A453-6EE77F760EBE</MessageSentID>
</MessageReceived>
The way I was looking at, was shredding the XML into a table, adding the new data then recreating the XML in the original structure. I've struggled to get the new parts to come into the correct part of the XML, so when I validate against the XSD, it fails.
DECLARE @XML AS XML
SET @XML = CONVERT(XML,'
<Message>
<MessageSentCollection>
<MessageSent ID="FB39C47F-B714-48CF-BA86-1D6805394D36">
<MessageName>RE72 3NS</MessageName>
</MessageSent>
<MessageSent ID="41111363-8726-4512-A850-F44277F1FF8F">
<MessageName>RL43 1SY</MessageName>
</MessageSent>
<MessageSent ID="13F0A6C9-5BA9-493B-AAD0-136EF9457902">
<MessageName>BV78 9DC</MessageName>
</MessageSent>
</MessageSentCollection>
<MessageReceivedCollection>
<MessageReceived ID="829DB73E-4480-4A58-95C6-6DB90D9183B4">
<MessageName>BA14 7GR</MessageName>
<MessageSentID>FB39C47F-B714-48CF-BA86-1D6805394D36</MessageSentID>
</MessageReceived>
<MessageReceived ID="BFCD1E81-E20F-41B3-99ED-77F739ACD1BD">
<MessageName>MC83 3EF</MessageName>
<MessageSentID>41111363-8726-4512-A850-F44277F1FF8F</MessageSentID>
</MessageReceived>
<MessageReceived ID="CE55ECBB-B9AF-4168-805C-BD63E7588453">
<MessageName>ZG67 5ZP</MessageName>
<MessageSentID>13F0A6C9-5BA9-493B-AAD0-136EF9457902</MessageSentID>
</MessageReceived>
</MessageReceivedCollection>
</Message>')
SELECT @XML

You should be able to modify the XML directly

SET @XML.modify('

insert <MessageSent ID="F11E4BAD-6405-49A6-A453-6EE77F760EBE">

<MessageName>CC83 2OK</MessageName>

</MessageSent>

as last into (/Message/MessageSentCollection)[1]

')

SELECT @XML

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537