Robert Buckley Robert Buckley - 4 months ago 10
SQL Question

How to add unique ID to an XML formated MSSQL SELECT query with DISTINCT clause?

I want to create a simple MSSQL

SELECT
query and format it in order to create an XML document.

I can format the XML simple in the following way:

SELECT

'<UNB_TIERART uuid="MY_UNIQUE_ID" > <TIERKLASSE>'

GRUPPE

+'</TIERKLASSE> </UNB_TIERART>'

FROM [MAPAGENT].[dbo].[ARTEN]


Is there a method to replace
MY_UNIQUE_ID
with a variable e.g a number which increases by one for each entry?

Answer

If you want to create xml document, why not entirely in SQL Server using following FOR XML syntax?

Take a look at this solution:

SELECT
ROW_NUMBER() OVER (ORDER BY Gruppe) AS '@uuid',
GRUPPE AS 'TIERKLASSE'
FROM (VALUES('G1'), ('G2')) T(Gruppe) [MAPAGENT].[dbo].[ARTEN]
FOR XML PATH('UNB_TIERART')

If you also need a root (full XML), just append ROOT('root name') like below:

SELECT
ROW_NUMBER() OVER (ORDER BY Gruppe) AS '@uuid',
GRUPPE AS 'TIERKLASSE'
FROM (VALUES('G1'), ('G2')) T(Gruppe) --YOUR TABLE
FOR XML PATH('UNB_TIERART'), ROOT('ROOT')

The latter produces:

<ROOT>
  <UNB_TIERART uuid="1">
    <TIERKLASSE>G1</TIERKLASSE>
  </UNB_TIERART>
  <UNB_TIERART uuid="2">
    <TIERKLASSE>G2</TIERKLASSE>
  </UNB_TIERART>
</ROOT>

If you need distinct gruppe, apply distinct to your base table:

SELECT
ROW_NUMBER() OVER (ORDER BY Gruppe) AS '@uuid',
GRUPPE AS 'TIERKLASSE'
FROM (
    SELECT DISTINCT Gruppe FROM [YOUR TABLE]
) DistincResult
FOR XML PATH('UNB_TIERART'), ROOT('ROOT')
Comments