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

Unfortunately you cannot do this with the SQL Server out of the box nor exists an elegant way to do that. To alleviate the issue, you can replace NULLs with empty strings. This will remove xmlns, but you have to define your select list explicitly as follows. Moreover, this works only with character string data types as you cannot assign an empty string ('' in ISNULL function) to-for example-an integer.

;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 
             ISNULL(VTR_UID, '')     'row/VTR_UID'
            ,ISNULL(VTR_MDT_ID, '')  'row/VTR_MDT_ID'
            ,ISNULL(VTR_VTP_UID, '') 'row/VTR_VTP_UID'
            ,ISNULL(VTR_Nr, '')      'row/VTR_Nr'
        FROM T_Contracts
        FOR XML PATH(''), TYPE
    )
FOR XML PATH('table'), TYPE, ELEMENTS xsinil

The result will be like below:

<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 />
    <VTR_Nr>0050/132251</VTR_Nr>
  </row>
</table>

Answer 2

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>

Answer 3

Why don't you build manually the root element ?

Example:

with CTE as (
  select (select * from T_Contracts for xml path('row')) as MyXML
)
select '<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" tableName="T_Contracts">' +
        MyXML + 
        '</table>'  
from CTE