Add a namespaces header in a SQL for XML query

Question

I have the following query

SELECT DISTINCT
    t.Code AS Prime,
    t.nom AS Alfa,
    t.Nom AS Name,
    t.Adresse AS Street,
    t.CP AS ZipCode,
    t.Localite AS City, 
    CASE t.CodePays 
        WHEN NULL THEN 'BE' 
        WHEN '' THEN 'BE' 
        ELSE t.CodePays 
    END AS Country,
    CASE t.CodeLangue 
        WHEN 'NL' THEN 1 
        WHEN 'FR' THEN 2 
        WHEN 'EN' THEN 3 
        WHEN 'DE' THEN 4 
        ELSE 1 
    END AS Language,
    'EUR' AS CurrencyCode,
    CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATCode,
    CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATStatus,
    t.Tva AS VATNumber,
    CASE t.CodePays 
        WHEN NULL THEN 'BE' 
        WHEN '' THEN 'BE' 
        ELSE t.CodePays 
    END AS CountryVATNumber,
    0 AS Status /* 0=pas importé*/ 
FROM 
    tiers t 
INNER JOIN 
    tiersexport te ON t.code = te.code 
WHERE 
    t.CodeTypeTiers IN (1, 3)
ORDER BY 
    t.Code 
FOR XML PATH('Customer'), ROOT('Customers');

This generates me a proper XML:

<Customers>
  <Customer>
  ...
  </Customer>
</Customers>

I need instead to have

<?xml version="1.0" encoding="ISO-8859-1"?>
<ImportExpMPlus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Customers>
        <Customer>
        ...
        </Customer>
    </Customers>
</ImportExpMPlus>

Can you tell me how to do this?

I tried

WITH XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema' AS xsd,'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT DISTINCT
    t.Code AS Prime,
    t.nom AS Alfa,
    t.Nom AS Name,
    t.Adresse AS Street,
    t.CP AS ZipCode,
    t.Localite AS City, 
    CASE t.CodePays 
        WHEN NULL THEN 'BE' 
        WHEN '' THEN 'BE' 
        ELSE t.CodePays 
    END AS Country,
    CASE t.CodeLangue 
        WHEN 'NL' THEN 1 
        WHEN 'FR' THEN 2 
        WHEN 'EN' THEN 3 
        WHEN 'DE' THEN 4 
        ELSE 1 
    END AS Language,
    'EUR' AS CurrencyCode,
    CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATCode,
    CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATStatus,
    t.Tva AS VATNumber,
    CASE t.CodePays 
        WHEN NULL THEN 'BE' 
        WHEN '' THEN 'BE' 
        ELSE t.CodePays 
    END AS CountryVATNumber,
    0 AS Status /* 0=pas importé*/ 
FROM 
    tiers t 
INNER JOIN 
    tiersexport te ON t.code = te.code 
WHERE 
    t.CodeTypeTiers IN (1, 3)
ORDER BY 
    t.Code 
FOR XML PATH('Customer'), ROOT('Customers');

but THEN I received this output:

<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Customer>
      ...
    </Customer>
</Customers>

Answer 1

Add the extra elements through variables, like this:

DECLARE @xml_header NVARCHAR(MAX);
SET @xml_header = N'<?xml version="1.0" encoding="ISO-8859-1"?>\n<ImportExpMPlus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">\n';
DECLARE @xml_footer NVARCHAR(MAX);
SET @xml_footer = N'\n</ImportExpMPlus>';

DECLARE @xml_data NVARCHAR(MAX);
SET @xml_data = (SELECT DISTINCT
                      t.Code AS Prime
                    , t.nom AS Alfa
                    , t.Nom AS Name
                    , t.Adresse AS Street
                    , t.CP AS ZipCode
                    , t.Localite AS City
                    , CASE t.CodePays WHEN NULL THEN 'BE' WHEN '' THEN 'BE' ELSE t.CodePays END AS Country
                    , CASE t.CodeLangue WHEN 'NL' THEN 1 WHEN 'FR' THEN 2 WHEN 'EN' THEN 3 WHEN 'DE' THEN 4 ELSE 1 END AS LANGUAGE
                    , 'EUR' AS CurrencyCode
                    , CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATCode
                    , CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATStatus
                    , t.Tva AS VATNumber
                    , CASE t.CodePays WHEN NULL THEN 'BE' WHEN '' THEN 'BE' ELSE t.CodePays END AS CountryVATNumber
                    , 0 AS STATUS
                FROM tiers t
                -- INNER JOIN tiersexport te ON t.code=te.code
                WHERE t.CodeTypeTiers IN (1, 3)
                ORDER BY t.Code
                FOR XML PATH('Customer')
                    , ROOT('Customers')
                )

SELECT @xml_header + @xml_data + @xml_footer

Note: I see no obvious purpose for the joined table tiersexport and maybe you can also get rid of DISTINCT because the join may be the cause of repeated rows.

see this dbfiddle: https://dbfiddle.uk/eNKqdjlY

Answer 2

Based on the answer by Paul ( https://stackoverflow.com/a/75903833/7733418 ) I provide a variation which might be needed in some cases:
I for example needed some conversion from XML to nvarchar and back to make it work

SELECT @Xml1 = @xml_header + convert(nvarchar(max),@xml_data) + @xml_footer
select CONVERT(XML,@Xml1)