CODEWITHSUNDEEP
sqlsql-serverxml
Woody
Woody

Reputation: 1787

Convert row values into XML tag names and values

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

Upvotes: 2

Views: 105

Answers (1)

Charlieface
Charlieface

Reputation: 72119

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

Upvotes: 1

Related Questions