在SQL for XML查询中添加名称空间头

Question

我有以下查询

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');

生成一个合适的XML:

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

我需要的是

<?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>

你能告诉我怎么做吗?

我试着

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');

但是随后我收到了这个输出:

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

Answer 1

通过变量添加额外元素，如下所示:

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

注意:我认为连接表tiersexport没有明显的目的，也许你也可以摆脱DISTINCT，因为连接可能会导致重复的行。

查看dbfiddle: https://dbfiddle.uk/eNKqdjlY