Sam Dahan Sam Dahan - 3 months ago 13
SQL Question

T-SQL/XML Query to get for each row in the query a subfield as well as the whole lement

I have an XML string like this:

<Root>
<Elem>
<RecTime>2016-08-17 12:30PM</RecTime>
<Otherfield>blah blah</Otherfield>
.. other fields ..
</Elem>
<Elem>
<RecTime>2016-08-17 15:30PM</RecTime>
<Otherfield>more blah</Otherfield>
.. other fields ..
</Elem>
</Root>


Obviously this describes a list of elements. I want to extract the record time as well as the entire element for every element in the XML - this is because I want to insert in a table the record time as well as the entire element: the table could be declared as

DECLARE Table myTable(SampleTime datetime, Data xml)


I tried the query

declare @xml xml
set @xml='<Root>
<Elem>
<RecordTime>2016-08-17 12:30:00PM</RecordTime>
<Otherfield>2</Otherfield>
<field2/>
</Elem>
<Elem>
<RecordTime>2016-08-17 15:30:00PM</RecordTime>
<Otherfield>3</Otherfield>
<field2>hello there</field2>
</Elem>
</Root>'

--INSERT INTO myTable
SELECT SampleTime = T.Item.value('RecordTime[1]', 'dateTime'),
Data = @xml.query('//Root/Elem[1]')
FROM @xml.nodes('//Root/Elem') T(item)


but it gives me rows that contain the proper time for each row but only the first element in the list for the 'whole element' part of the query:


I circled in red the proof that I select the wrong element
How should I shape the query to get in response the sample time for each element as well as the corresponding element?

Thanks for the help!

Answer

The issue is in Data = @xml.query('//Root/Elem[1]'). To achieve what you want use something like this.

--INSERT INTO myTable
SELECT SampleTime = T.Item.value('RecordTime[1]', 'dateTime'),
            Data = T.item.query('.')
        FROM @xml.nodes('//Root/Elem') T(item)

This is result.

2016-08-17 12:30:00.000 <Elem><RecordTime>2016-08-17 12:30:00PM</RecordTime><Otherfield>2</Otherfield><field2 /></Elem>
2016-08-17 15:30:00.000 <Elem><RecordTime>2016-08-17 15:30:00PM</RecordTime><Otherfield>3</Otherfield><field2>hello there</field2></Elem>
Comments