How to transform XML data into SQL Server Table

Question

I have a XML data like this :

<root>
 <log realm="ABC" at="Wed Oct 15 00:00:02 2014.211" lifespan="2279ms">
  <receive>
    <isomsg direction="IN">
      <header>6000911384</header>
      <field id="0" value="0800"/>
      <field id="3" value="980000"/>
      <field id="11" value="000852"/>
    </isomsg>
  </receive>
</log>
</root>

how can I transform that XML data into table like this :

    AT         |lifespan|direction |ID |Value 
---------------------------------------------
Wed Oct 15 2014|2279ms  |in        |0  |0800
Wed Oct 15 2014|2279ms  |in        |3  |980000
Wed Oct 15 2014|2279ms  |in        |11 |000852

You could also look into creating an xslt to transform the data into an SQL script — user2033671
– user2033671, Commented Oct 15, 2014 at 18:19

marc_s · Accepted Answer · 2014-10-15 19:37:06Z

4

This would be a lot easier than @Nick's answer, since it only needs one .nodes() call instead of three nested ones...

DECLARE @input XML = '<root>
 <log realm="ABC" at="Wed Oct 15 00:00:02 2014.211" lifespan="2279ms">
  <receive>
    <isomsg direction="IN">
      <header>6000911384</header>
      <field id="0" value="0800"/>
      <field id="3" value="980000"/>
      <field id="11" value="000852"/>
    </isomsg>
  </receive>
</log>
</root>'

SELECT
    At = xc.value('../../../@at', 'varchar(50)'),
    Lifespan = xc.value('../../../@lifespan', 'varchar(25)'),
    Direction = xc.value('../@direction', 'varchar(10)'),
    ID = XC.value('@id', 'int'),
    Value = xc.value('@value', 'varchar(25)')
FROM
    @Input.nodes('/root/log/receive/isomsg/field') AS XT(XC)

The call to @Input.nodes basically returns a "virtual" table of XML fragments, representing each of the <field> XML elements. By using the .. we can also navigate "up the" XML hierarchy in the original document to access the <isomsg> and <log> elements and grab attribute values from those

answered Oct 15, 2014 at 19:37

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

2 Comments

Nick H. Over a year ago

Nice!! Way cleaner than mine. I assume this works when selecting the data from a table (opposed to a variable with XML data)? I only ask because I originally had something simliar to this but had trouble getting it to work. I started the nodes from the top level though /root/log and that could have been the issue

Eddy Yakup Over a year ago

Wow. thats simple. Thank you very much marc_s, Thank you very much Nick

Nick H. · Accepted Answer · 2014-10-15 18:28:43Z

Here is how I did it (although there are several ways).

WITH xmlData AS (
SELECT CAST('<root>
 <log realm="ABC" at="Wed Oct 15 00:00:02 2014.211" lifespan="2279ms">
  <receive>
    <isomsg direction="IN">
      <header>6000911384</header>
      <field id="0" value="0800"/>
      <field id="3" value="980000"/>
      <field id="11" value="000852"/>
    </isomsg>
  </receive>
</log>
</root>' AS XML) as xmlData)
SELECT xmlData, logs.x.value('(@at)[1]','varchar(50)') as 'Element1', logs.x.value('(@lifespan)[1]','varchar(50)') as 'Element2', ismsgs.logs.value('(@direction)[1]','varchar(50)') as 'Element3', fields.ismsgs.value('(@value)[1]','varchar(50)') as 'Element4'
FROM xmlData x
CROSS APPLY xmlData.nodes('root/log') logs(x)   
CROSS APPLY xmlData.nodes('root/log/receive/isomsg') ismsgs(logs)
CROSS APPLY xmlData.nodes('root/log/receive/isomsg/field') fields(ismsgs)

Collectives™ on Stack Overflow

How to transform XML data into SQL Server Table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related