xml - 从 SQL 查询数据结果创建个性化 XML

Question

我正在尝试通过具有特定格式的 SQL 数据结果创建 xml 结果。但我不知道我将如何生成。我知道一种基本的打电话方式

目前按照下面的 Sql Query

DECLARE @cityFrom VARCHAR(10)= 'Berlin';
DECLARE @CustomerFrom VARCHAR(25)= CASE
     WHEN @cityFrom = 'Berlin'
     THEN 'ID_1'
     WHEN @cityFrom = 'London'
     THEN 'ID_2'
     WHEN @cityFrom = 'Tsawassen'
     THEN 'ID_3'
     WHEN @cityFrom = 'Mannheim'
     THEN 'ID_4'
 END;

    SELECT CustomerID,
       CustomerName,
       Country
    FROM Customers
    WHERE City = @cityFrom;
FOR XML PATH (''), ROOT('table') 

我得到这个输出

<table>
  <CustomerID>1</CustomerID>
  <CustomerName>Alfreds Futterkiste</CustomerName>
  <Country>Germany</Country>
</table>

但我想得到我的预期结果如下，

<ID_1>
    <Parameter key="ID_1_CustomerID">1</Parameter>
    <Parameter key="ID_1_CustomerName">Alfreds Futterkiste</Parameter>
    <Parameter key="ID_1_Country">Germany</Parameter>
</ID_1>

我打电话给@CustomerFrom，因为我想将其发布为<ID_1>...</ID_1>. 我也知道<table>...</table>是通过 ROOT('table') 发布的，但我无法通过 ROOT(@CustomerFrom) 获取...

问题：有什么解决方案如何获得我的预期结果？

标签： xmlsql-server-2012

Answer 1

用变量命名元素是个坏主意（稍后会详细介绍）。但这可以解决：

DECLARE @mockup TABLE(CustomerID INT, CustomerName VARCHAR(100),Country VARCHAR(100),City VARCHAR(100));
INSERT INTO @mockup VALUES(99,'TestName','Germany','Berlin');

DECLARE @cityFrom VARCHAR(10)= 'Berlin';

DECLARE @CustomerFrom VARCHAR(25)= CASE
     WHEN @cityFrom = 'Berlin'
     THEN 'ID_1'
     WHEN @cityFrom = 'London'
     THEN 'ID_2'
     WHEN @cityFrom = 'Tsawassen'
     THEN 'ID_3'
     WHEN @cityFrom = 'Mannheim'
     THEN 'ID_4'
 END;

SELECT @CustomerFrom + '_CustomerID' AS [Parameter/@key]
       ,CustomerID AS [Parameter]
       ,''
       ,@CustomerFrom + '_CustomerName' AS [Parameter/@key]
       ,CustomerName AS [Parameter]
       ,''
       ,@CustomerFrom + '_Country' AS [Parameter/@key]
       ,Country AS [Parameter]
FROM @mockup
FOR XML PATH('ReplaceThis');

在下一步中，您必须将其转换为NVARCHAR(MAX)并用于REPLACE进入ID_1根节点...

我不知道，如果预期的结果需要这样......

将信息（内容）推送到元素的名称中是一个非常糟糕的主意。而不是<ID_1>我建议像<ID location="1">.

而不是ID_1_CustomerID我会使用两个属性或从上面的级别获取这些信息。

尝试这个：

SELECT @CustomerFrom AS [@location]
       ,'CustomerID' AS [Parameter/@key]
       ,CustomerID AS [Parameter]
       ,''
       ,'CustomerName' AS [Parameter/@key]
       ,CustomerName AS [Parameter]
       ,''
       ,'Country' AS [Parameter/@key]
       ,Country AS [Parameter]
FROM @mockup
FOR XML PATH('ID');

结果

<ID location="ID_1">
  <Parameter key="CustomerID">99</Parameter>
  <Parameter key="CustomerName">TestName</Parameter>
  <Parameter key="Country">Germany</Parameter>
</ID>