How to specify attributes on root element?

Question

Short version

Using SQL Server FOR XML ROOT('Customers'), how to I add attributes to that root node?

<Customers> ← attributes here
   <Customer>Ian</Customer>
   <Customer>Shelby</Customer>
   <Customer>Dave</Customer>
</Customers>

Long Version

When using FOR XML in SQL Server:

SELECT *
FROM (VALUES
    (122, 'All-Purpose Bike Stand'),
    (119, 'Bike Wash'),
    (115, 'Cable Lock')
) AS Products(ProductModelID, Name)
FOR XML PATH('Product')

it normally simply returns the elements:

<Product>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</Product>
<Product>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</Product>
<Product>
  <ProductModelID>115</ProductModelID>
  <Name>Cable Lock</Name>
</Product>

(3 rows affected)

That's not a valid XML document, because there are three top-level nodes - rather than just one.

That can be fixed by specifying ROOT('RootNodeName'):

SELECT *
FROM (VALUES
    (122, 'All-Purpose Bike Stand'),
    (119, 'Bike Wash'),
    (115, 'Cable Lock')
) AS Products(ProductModelID, Name)
FOR XML PATH('Product'), ROOT('Products')

<Products>
  <Product>
    <ProductModelID>122</ProductModelID>
    <Name>All-Purpose Bike Stand</Name>
  </Product>
  <Product>
    <ProductModelID>119</ProductModelID>
    <Name>Bike Wash</Name>
  </Product>
  <Product>
    <ProductModelID>115</ProductModelID>
    <Name>Cable Lock</Name>
  </Product>
</Products>

(3 rows affected)

Excellent.

Except Attributes

The above is great, but I'm not done with the XML document I need to generate. I need add some attributes to the root node:

<Products operationalMode="Test" batchDate="2021-02-15T17:36:22" formatId="8e884ace-bee4-11e4-8dfc-aa07a5b093db">
  <Product>
    <ProductModelID>122</ProductModelID>
    <Name>All-Purpose Bike Stand</Name>
  </Product>
  <Product>
    <ProductModelID>119</ProductModelID>
    <Name>Bike Wash</Name>
  </Product>
  <Product>
    <ProductModelID>115</ProductModelID>
    <Name>Cable Lock</Name>
  </Product>
</Products>

How do I add attributes to the XML ROOT('rootNode') element?

Answer 1

Please try the following solution.

XML should be at minimum well-formed. To be valid it needs an XML Schema.

It is not clear what is the source for the attributes, so I just hard-coded their values.

As you see, we need to apply FOR XML PATH clause twice. Once for the 'inner' XML. And second time for the root element, and specified attributes via aliases with at signs.

SQL

SELECT 'Test'AS [@operationalMode]
    , '2021-02-15T17:36:22' AS [@batchDate]
    , '8e884ace-bee4-11e4-8dfc-aa07a5b093db' AS [@formatId]
, (
    SELECT *
    FROM (VALUES
        (122, 'All-Purpose Bike Stand'),
        (119, 'Bike Wash'),
        (115, 'Cable Lock')) AS Products(ProductModelID, Name)
    FOR XML PATH('Product'), TYPE
)
FOR XML PATH('Products'), TYPE;

Output

<Products operationalMode="Test" batchDate="2021-02-15T17:36:22" formatId="8e884ace-bee4-11e4-8dfc-aa07a5b093db">
  <Product>
    <ProductModelID>122</ProductModelID>
    <Name>All-Purpose Bike Stand</Name>
  </Product>
  <Product>
    <ProductModelID>119</ProductModelID>
    <Name>Bike Wash</Name>
  </Product>
  <Product>
    <ProductModelID>115</ProductModelID>
    <Name>Cable Lock</Name>
  </Product>
</Products>