Convert row values into XML tag names and values

Question

I am using SQL Server 2019 and I have a table like the following:

DROP TABLE IF EXISTS #Temp
SELECT *
INTO #Temp
FROM (
SELECT 1 Id, 'Name' ParameterName, 'John Smith' ParameterValue UNION
SELECT 1 Id, 'Age' ParameterName, '20' ParameterValue UNION 
SELECT 1 Id, 'Gender' ParameterName, 'Male' ParameterValue UNION
SELECT 2 Id, 'Name' ParameterName, 'Jane Smythe' ParameterValue UNION
SELECT 2 Id, 'Age' ParameterName, '26' ParameterValue UNION 
SELECT 2 Id, 'Org Group' ParameterName, 'Marketing' ParameterValue UNION
SELECT 2 Id, 'Gender' ParameterName, 'Female' ParameterValue) t
ORDER BY 1, 2

And I am wanting a query to transform this table into two rows with the Id, and an xml column with the value of the ParameterName as the xml tag and the value of the ParameterValue as the xml value e.g.:

<Age>20</Age>
<Gender>Male</Gender>
<Name>John Smith</Name>

I have tried the following but its not quite right:

SELECT  t.Id
    , (SELECT       t1.ParameterName  
                , t1.ParameterValue
        FROM        #Temp t1
        WHERE       t1.Id = t.Id
        FOR XML AUTO, TYPE)
FROM    #Temp t
GROUP BY t.Id



SELECT  t.Id
    , (SELECT       t1.ParameterName  
                , t1.ParameterValue
        FROM        #Temp t1
        WHERE       t1.Id = t.Id
        FOR XML RAW, ELEMENTS, TYPE)
FROM    #Temp t
GROUP BY t.Id


SELECT  t.Id
    , (SELECT       t1.ParameterName  
                , t1.ParameterValue
        FROM        #Temp t1
        WHERE       t1.Id = t.Id
        FOR XML PATH (''), TYPE)
FROM    #Temp t
GROUP BY t.Id

Do you know all possible ParameterName values up front? Otherwise it's very hard to do this accurately in SQL Server — Charlieface
– Charlieface, Commented Jun 14, 2022 at 0:55
@lptr That doesn't work well if there are characters that need escaping — Charlieface
– Charlieface, Commented Jun 14, 2022 at 0:56
@CharlieFace, no I don't know the list of all possible ParameterNames — Woody
– Woody, Commented Jun 14, 2022 at 0:58
@woody…try the query with cast() only [instead of try_cast()]..if there are parameternames which cannot be xml tags..an error will be raised..(try_cast() silences any parameternames which cannot appear as xml elements) — lptr
– lptr, Commented Jun 14, 2022 at 1:06

Charlieface · Accepted Answer · 2022-06-14 01:23:29Z

If you know all the possible ParameterName values up-front, you can just do conditional aggregation.

SELECT
  t.Id,
  MAX(CASE WHEN ParameterName = 'Name'      THEN ParameterValue END) Name,
  MAX(CASE WHEN ParameterName = 'Age'       THEN ParameterValue END) Age,
  MAX(CASE WHEN ParameterName = 'Gender'    THEN ParameterValue END) Gender,
  MAX(CASE WHEN ParameterName = 'Org Group' THEN ParameterValue END) Org_Group
FROM #Temp t
GROUP BY
  t.Id
FOR XML PATH('Node'), ROOT('Root')

db<>fiddle

Without knowing the node names up-front, it becomes much harder, because neither FOR XML nor .query construction allow dynamic node names.

You need to construct it yourself, and you need to make sure to escape all necessary values.

In the below code, ParameterValue is escaped, but ParameterName only has spaces removed. You may need to check for other characters also.

SELECT
  CAST(
    CONCAT(
      '<Node>',
      STRING_AGG(
        CONCAT(
          '<',
          REPLACE(ParameterName, ' ', '_'),
          '>',
          x.txt,
          '</',
          REPLACE(ParameterName, ' ', '_'),
          '>'
        ),
        ''
      ),
      '</Node>'
    ) AS xml
  )
FROM #Temp t
CROSS APPLY (
    SELECT ParameterValue [text()]
    FOR XML PATH('')
) x(txt)
GROUP BY
  t.Id
FOR XML PATH(''), ROOT('Root');

db<>fiddle

Collectives™ on Stack Overflow

Convert row values into XML tag names and values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related