Parsing and joining XML using xquery

Question

Post reply

Parsing and joining XML using xquery

Maddave

SSCertifiable

Points: 6828
More actions
August 4, 2016 at 4:08 am

#328632

I have a table which has an xml column which contains multiple xml elements as follows:
CREATE TABLE ReportData ( id INT, ReportData XML );
There is a second table called UIElements which lists a description of the xml names space descriptions. There is no physical relationship between this table or the first table:
CREATE TABLE UIElements
(
id INT ,
ElementDescription VARCHAR(50) ,
Element VARCHAR(50)
);
Sample data of ReportData:
INSERT INTO dbo.ReportData
( id ,
ReportData
)
VALUES ( 1 ,
'<F7d34f773afe84c988756c0a5da129bed xmlns="http://www.Example.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DataType>date</DataType>
<Value>2016-07-01T00:00:00Z</Value>
<Archived>false</Archived>
</F7d34f773afe84c988756c0a5da129bed>
<F2257960efbc74b309adace69587a943b xmlns="http://www.Example.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DataType>list-reference</DataType>
<Value>285</Value>
<Archived>false</Archived>
</F2257960efbc74b309adace69587a943b>'
);
Sample data of Elements table: This is just one row, but in reality the table contains multiple rows for lots of different records, each with multiple namespace elements contained in the xml ReportData column.
INSERT INTO dbo.UIElements
( id ,
ElementDescription ,
Element
)
VALUES ( 1 , -- id - int
'Report Date' , -- ElementDescription - varchar(50)
'F7d34f773afe84c988756c0a5da129bed' -- Element - varchar(50)
),
( 2, -- id - int
'Report Number' , -- ElementDescription - varchar(50)
'F2257960efbc74b309adace69587a943b' -- Element - varchar(50)
)
I can use the following code to select and extract the <Value> element of the ReportData table for a specific element:
;
WITH XMLNAMESPACES ('http://www.example.com' AS ns)
SELECT id, Valuexml = ReportData.ReportData.value('(/ns:F2257960efbc74b309adace69587a943b/ns:Value)[1]', 'varchar(1000)')
FROM dbo.ReportData;
However, as one row of the report data table can have multiple <value> elements for each of the different UIElements, I need to be able to select all iterations from the ReportData table. The output would look like this:
ReportDataID Element Value
1 F7d34f773afe84c988756c0a5da129bed 2016-07-01T00:00:00Z
1 F2257960efbc74b309adace69587a943b 285
2 F7d34f773afe84c988756c0a5da129bed 2016-07-02T00:15:00Z
2 F2257960efbc74b309adace69587a943b 290
3 F7d34f773afe84c988756c0a5da129bed 2016-04-01T00:00:00Z
I'm struggling to work out how to select this data without running some kind of loop which reads through each UIElement, dynamically build the Xquerires, output the results to a temporary table, then select from that with an order by to get everything in order.
Any tips?

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

Try this

SELECT rd.id,

x.r.value('local-name(.)','varchar(30)') AS Element,

x.r.value('(*:Value/text())[1]','varchar(30)') AS Value

FROM dbo.ReportData rd

CROSS APPLY rd.ReportData.nodes('/*') x(r);

____________________________________________________

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

Maddave SSCertifiable Points: 6828 More actions · Answer 2

Excellent. That works fine. Just what I need.

Thanks.