How to convert XML data to SQL Server table
I have 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 convert XML data to 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
source to share
This would be much simpler than @ Nick's answer since it only needs one call .nodes()
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 @Input.nodes
basically returns a "virtual" XML fragment table representing each of the XML elements <field>
. Using ..
, we can also navigate the XML hierarchy in the source document to access the elements <isomsg>
and <log>
and get the grab attribute values from these
source to share
This 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)
source to share