CODEWITHSUNDEEP
sql-servert-sqlsql-server-2019for-xmlselect-for-xml
Paul White
Paul White

Reputation: 374

Generating self-closing XML tags for lists in SQL Server

I saw this XML example data in a 2013 SQL Server question on Stack Overflow:

<Fruits>
  <Apple />
  <Banana />
  <Orange />
  <Pear />
</Fruits>

I'm more used to seeing such lists structured as (for example):

<Fruits>
  <Fruit name="Apple" />
  <Fruit name="Banana" />
  <Fruit name="Orange" />
  <Fruit name="Pear" />
</Fruits>

I struggled to produce the (textually more compact) self-closing list format using FOR XML in SQL Server, eventually settling on:

SELECT 
    CONVERT(xml, 
        (
            SELECT 
                Apple = '', 
                Banana = '', 
                Orange = '', 
                Pear = ''
            FOR XML RAW ('Fruits'), ELEMENTS
        )
    );

Using the TYPE directive instead of converting the subquery result to xml didn't produce self-closing elements (though the result is logically equivalent):

<Fruits>
  <Apple></Apple>
  <Banana></Banana>
  <Orange></Orange>
  <Pear></Pear>
</Fruits>

Is there a neater way to produce the list in self-closing form using FOR XML in SQL Server?

db<>fiddle

There's no real-world problem to be solved here — I'm simply curious.

Upvotes: 0

Views: 483

Answers (4)

lptr
lptr

Reputation: 6798

Casting '' as XML returns a self closing element or query('.') the generated XML, TYPE is required.

SELECT 
    Apple = '', 
    Banana = cast('' as xml), 
    Orange = '', 
    Pear = cast('' as xml)
FOR XML RAW ('Fruits'), ELEMENTS, TYPE;

SELECT 
(SELECT 
    Apple = '', 
    Banana = '', 
    Orange = '', 
    Pear = ''
FOR XML RAW ('Fruits'), ELEMENTS, TYPE).query('.');

Upvotes: 4

Paul White
Paul White

Reputation: 374

Based on the answer I accepted, I went with:

DECLARE @x xml = CONVERT(xml, N'');

SELECT 
    Apple = @x, 
    Banana = @x, 
    Orange = @x,
    Pear = @x
FOR XML RAW ('Fruits'), 
    ELEMENTS, 
    TYPE;

execution plan

Tags can be rendered in different ways depending on where and how the rendering is done. This can be in the query processor, specific FOR XML server-side code, or even by the client driver in some cases.

Details in What does server side FOR XML return? by the SQL Server Programmability Team.

Upvotes: 1

Charlieface
Charlieface

Reputation: 72194

It's not so much the self-closing tags you want, as it is semantically equivalent to a non-self-closing one anyway. It's that you want your data as attributes rather than elements.

It's much easier with FOR XML PATH. Normally you would have your date in a table, but I've shown you with a VALUES virtual table

SELECT 
    v.Name AS [@Name]
FROM (VALUES
    ('Apple'), 
    ('Banana'), 
    ('Orange'), 
    ('Pear')
) v(Name)
FOR XML PATH('Fruit'), ROOT('Fruits'), TYPE;

db<>fiddle

This does also produce self-closing tags, somewhat as a by-product.

Upvotes: 0

Charlieface
Charlieface

Reputation: 72194

Assuming you were actually asking for FOR XML to generate self-closing tags, you can get this using CAST('' AS xml)

SELECT 
  Apple = CAST('' AS xml), 
  Banana = CAST('' AS xml), 
  Orange = CAST('' AS xml),
  Pear = CAST('' AS xml)
FOR XML RAW ('Fruits'), ELEMENTS, TYPE;

db<>fiddle

You can also do this using FOR XML PATH

SELECT 
    CAST('' AS xml) Apple, 
    CAST('' AS xml) Banana, 
    CAST('' AS xml) Orange, 
    CAST('' AS xml) Pear
FOR XML PATH('Fruit'), ROOT('Fruits'), TYPE;

db<>fiddle

Note that because they are semantically equivalent, any good XML parser will not see a difference, and may freely convert between one and the other without you noticing.

Upvotes: 2

Related Questions