How Can I Add Constants To My Root In Xml?

This is my query that generates an XML: SELECT [a] a ,[b] b ,[c] c ,[d]

Solution 1:

Here is a conceptual example. Please give it a shot.

SQL

-- DDL and sample data population, startDECLARE@tblTABLE (ID INTIDENTITYPRIMARY KEY, city VARCHAR(30));
INSERTINTO@tbl (city) VALUES
('Miami'),
('Orlando');
-- DDL and sample data population, endSELECT'SIN_OPE'AS [@cod_1], '08'AS [@cod_2], '12'AS [@num_reg]
    , 'yyyyyyyyyyyyyyyyyyy.xsd'AS [@xsi:noNamespaceSchemaLocation]
, (
    SELECT*FROM@tblFOR XML PATH('r'), TYPE
)
FOR XML PATH('root'), TYPE, ELEMENTS XSINIL;

Output

<rootxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"cod_1="SIN_OPE"cod_2="08"num_reg="12"xsi:noNamespaceSchemaLocation="yyyyyyyyyyyyyyyyyyy.xsd"><r><ID>1</ID><city>Miami</city></r><r><ID>2</ID><city>Orlando</city></r></root>