How to put an attribute on the root element, and only the root element, in FOR XML PATH?

Question

I'm generating XML from a SQL Server table.

This is my code:

;WITH XMLNAMESPACES
( 
     'http://www.w3.org/2001/XMLSchema-instance' AS xsi 
    --,DEFAULT 'http://www.w3.org/2001/XMLSchema-instance'  -- xmlns 
) 
SELECT 
    'T_Contracts' AS "@tableName",
    (SELECT * FROM T_Contracts 
     FOR XML PATH('row'), TYPE, ELEMENTS xsinil)
FOR XML PATH('table'), TYPE, ELEMENTS xsinil

I want the result to look like this (note: attribute tableName on the root element):

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" tableName="T_Contracts">
  <row>
    <VTR_UID>779FE899-4E81-4D8C-BF9B-3F17BC1DF146</VTR_UID>
    <VTR_MDT_ID>0</VTR_MDT_ID>
    <VTR_VTP_UID xsi:nil="true" />
    <VTR_Nr>0050/132251</VTR_Nr>
  </row>
</table>

But it duplicates the XSI namespace on the row element...

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" tableName="T_Contracts">
  <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <VTR_UID>779FE899-4E81-4D8C-BF9B-3F17BC1DF146</VTR_UID>
    <VTR_MDT_ID>0</VTR_MDT_ID>
    <VTR_VTP_UID xsi:nil="true" />
    <VTR_Nr>0050/132251</VTR_Nr>
  </row>
</table>

What's the correct way to add an attribute to the root element, and only the root element ?

Note

NULL-values must be returned as <columnName xsi:nil="true" /> and not be omitted.

(And no xml.modify after the select)

Please note that this is NOT a duplicate of an existing question.

Answer 1

This annoying behaviour of repeated namespaces with sub-queries was a reported issue for more than 10 years on MS-Connect with thousands of votes. This platform was dismissed, so was this issue and there is no perspective that MS will ever solve this.

Just to be fair: It is not wrong to repeat the namespace declaration. It's just bloating the string-based output...

Even stranger is the the unsupported attribute on a root level node...

Well, if you need a head-ache, you might look into OPTION EXPLICIT :-)

The accepted answer by Marc Guillot will not produce xsi:nil="true" attributes as you seem to need them. It will just wrap your result with the appropriate root node.

Finally: This cannot be solved with XML methods, you can try this:
Update: Found a way, see below...

DECLARE @tbl TABLE(ID INT,SomeValue INT);
INSERT INTO @tbl VALUES(1,1),(2,NULL);

SELECT CAST(REPLACE(CAST(
(
    SELECT *
    FROM @tbl 
    FOR XML PATH('row'),ROOT('table'),TYPE, ELEMENTS XSINIL
) AS nvarchar(MAX)),'<table ','<table tableName="T_Contracts" ') AS XML);

The result

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" tableName="T_Contracts">
  <row>
    <ID>1</ID>
    <SomeValue>1</SomeValue>
  </row>
  <row>
    <ID>2</ID>
    <SomeValue xsi:nil="true" />
  </row>
</table>

The idea in short:

• We create the XML without a sub-query and add the attribute with a string method into the casted XML.
• As the position of an attribute is not important, we can add it everywhere.
• alternatively you might search for the first closing > and use STUFF() there...

UPDATE

Heureka, I just found a way, to create this without swithing to string, but it's clumsy :-)

DECLARE @tbl TABLE(ID INT,SomeValue INT);
INSERT INTO @tbl VALUES(1,1),(2,NULL);

SELECT
(
SELECT 'T_Contracts' AS [@tableName]
      ,(
        SELECT 'SomeRowAttr' AS [@testAttr] --added this to test row-level attributes
              ,*
        FROM @tbl 
        FOR XML PATH('row'),TYPE, ELEMENTS XSINIL
       )
FOR XML PATH('table'),TYPE, ELEMENTS XSINIL
).query('<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">{/table/@*}
         {
            for $nd in /table/row
            return
            <row>{$nd/@*}
            {
                $nd/*
            }
            </row>
         }
         </table>');

The result

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" tableName="T_Contracts">
  <row testAttr="SomeRowAttr">
    <ID>1</ID>
    <SomeValue>1</SomeValue>
  </row>
  <row testAttr="SomeRowAttr">
    <ID>2</ID>
    <SomeValue xsi:nil="true" />
  </row>
</table>