Special output from xml data with XQuery

Question

Post reply

Special output from xml data with XQuery

salliven

SSCrazy

Points: 2644
More actions
May 28, 2018 at 8:49 am

#346582

Hello,
Can you help me with this?
I have an xml data and I would like a special output with an XQuery.
-- XML data Declare @xml xml = N' <xml>     <row GroupID="1" TableName="Users">         <FirstName>John</FirstName>         <LastName>Smith</LastName>     </row>     <row GroupID="2" TableName="Users">         <FirstName>James</FirstName>         <LastName>Taylor</LastName>     </row> </xml>'
-- Query to xml data select     -- missing, I would like to see the GroupID,     -- missing, I would like to see the TableName,      q.x.value('local-name(.)', 'nvarchar(50)') as [Key],      q.x.value('(.)[1]', 'nvarchar(50)') as [Value] from @xml.nodes('//xml/row/*') q(x)
-- I would like this output:
TableName GroupID Key            Value --------- --------    ----------     ------- Users     1   FirstName      John Users    1           LastName       Smith Users     2           FirstName James Users     2   LastName       Taylor

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

This should get you passed this hurdle
😎

USE TEEST; GO SET NOCOUNT ON;

--https://www.sqlservercentral.com/Forums/1962235/Special-output-from-xml-data-with-XQuery

Declare @TXML XML = N' <xml> <row GroupID="1" TableName="Users"> <FirstName>John</FirstName> <LastName>Smith</LastName> </row> <row GroupID="2" TableName="Users"> <FirstName>James</FirstName> <LastName>Taylor</LastName> </row> </xml>'

SELECT TROW.DATA.value('@TableName' ,'VARCHAR(20)') AS TableName ,TROW.DATA.value('@GroupID' ,'INT') AS GroupID ,TLINE.DATA.value('local-name(.)', 'NVARCHAR(50)') AS [Key] ,TLINE.DATA.value('(./text())[1]', 'nvarchar(50)') AS [Value] FROM @TXML.nodes('xml/row') TROW(DATA) CROSS APPLY TROW.DATA.nodes('*') TLINE(DATA);

salliven SSCrazy Points: 2644 More actions · Answer 2

I guessed I have to you the CROSS APPLY, but I did't know how to.
So, thanks, I have a new pattern now 🙂

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

salliven - Tuesday, May 29, 2018 1:54 AM
I guessed I have to you the CROSS APPLY, but I did't know how to.
So, thanks, I have a new pattern now 🙂

You are very welcome.
😎

You can see the output of the second nodes function by using the query method, here is an example.

SELECT TROW.DATA.value('@TableName' ,'VARCHAR(20)') AS TableName ,TROW.DATA.value('@GroupID' ,'INT') AS GroupID ,TLINE.DATA.value('local-name(.)', 'NVARCHAR(50)') AS [Key] ,TLINE.DATA.value('(./text())[1]', 'nvarchar(50)') AS [Value] -- Add the line below to see the output of the cross applied -- second nodes function. ,TLINE.DATA.query('.') AS XML_CONTENT FROM @TXML.nodes('xml/row') TROW(DATA) CROSS APPLY TROW.DATA.nodes('*') TLINE(DATA);