多个XML属性

Question

我想生成一个格式如下的XML文件:

<ProData>
    <DataSet Name="ABCD">
        <Data DataElement="AAA" Value="10"/>
        <Data DataElement="BBB" Value="20"/>
    </DataSet>
    <DataSet Name="EFGH">
        <Data DataElement="CCC" Value="NAME"/>
        <Data DataElement="DDD" Value="SURNAME"/>
    </DataSet>
</ProData>

MyTable中的每一行在生成的XML中都应该有一个ProData记录。My Table is this

CREATE TABLE MyTable(
    [CustomerNumber] [nvarchar](6) NOT NULL,
    [AAA_Value] Int NOT NULL,
    [BBB_Value] Int NOT NULL,
    [Name]     [nvarchar](10) NOT NULL,
    [Surname] [nvarchar](10) NOT NULL 
)

INSERT [dbo].[MyTable] ([CustomerNumber], [AAA_Value], [BBB_Value], [Name], [Surname]) VALUES (N'123456', 10, 20, N'Phoebe', N'Buffay')
INSERT [dbo].[MyTable] ([CustomerNumber], [AAA_Value], [BBB_Value], [Name], [Surname]) VALUES (N'234567', 30, 40, N'Ross', N'Geller')

所以期望的输出应该是:

<ProData>
    <DataSet Name="ABCD">
        <Data DataElement="AAA" Value="10"/>
        <Data DataElement="BBB" Value="20"/>
    </DataSet>
    <DataSet Name="EFGH">
        <Data DataElement="CCC" Value="Phoebe"/>
        <Data DataElement="DDD" Value="Buffay"/>
    </DataSet>
</ProData>
<ProData>
    <DataSet Name="ABCD">
        <Data DataElement="AAA" Value="30"/>
        <Data DataElement="BBB" Value="40"/>
    </DataSet>
    <DataSet Name="EFGH">
        <Data DataElement="CCC" Value="Ross"/>
        <Data DataElement="DDD" Value="Geller"/>
    </DataSet>
</ProData>

我正在使用FOR XML PATH查询来检索我的XML文件，但由于以下错误，我无法成功检索多个嵌套元素:

同一个属性不能在同一个XML上生成多次标签。

我的查询如下:

(SELECT   
    blah,  
     'AAA'      as 'Common/Data/ProData/DataSet/Data/@DataElement',
    AAA_Value   AS 'Common/ApplicationData/ProData/DataSet/Data/@Value'     ,       
    'BBB'       as 'Common/Data/ProData/DataSet/Data/@DataElement' ,             
    BBB_Value   as 'Common/Data/ProData/DataSet/Data/@Value', 
    blah
FROM MyTable
FOR XML PATH('Notification'),ROOT('NotificationsList'),              
TYPE)
FOR XML PATH ('NotificationFile') 

我已经设法通过"强制"使用嵌套查询的结果集获得所需的结果，像这样:

(SELECT   blah,  
    (SELECT 
        (SELECT
            (SELECT 'AAA' AS 'Data/@DataElement' ,
                    AAA_Value   AS 'Data/@Value' 
                    FROM MyTable WHERE CONDITION
                    FOR xml path(''),   TYPE),
            (SELECT 'BBB' AS 'Data/@DataElement' ,
                    BBB_Value   AS 'Data/@Value' 
                    FROM MyTable WHERE CONDITION
                    FOR xml path(''),   TYPE),
            FOR xml path('DataSet'), TYPE)
            FOR xml path('ProData'), TYPE ) ,
    blah
    FROM MyTable
    FOR XML PATH('Notification'),ROOT('NotificationsList'),              
    TYPE      )
FOR XML PATH ('NotificationFile') 

我很确定这是一种糟糕的方法，但我似乎找不到更好的方法了。

Answer 1

在你明确了你的需求之后，我认为这就是你想要的

——你的表

CREATE TABLE MyTable(
    [CustomerNumber] [nvarchar](6) NOT NULL,
    [AAA_Value] Int NOT NULL,
    [BBB_Value] Int NOT NULL,
    [Name]     [nvarchar](10) NOT NULL,
    [Surname] [nvarchar](10) NOT NULL 
)

——测试数据

INSERT [dbo].[MyTable] ([CustomerNumber], [AAA_Value], [BBB_Value], [Name], [Surname]) 
VALUES (N'123456', 10, 20, N'Phoebe', N'Buffay')
INSERT [dbo].[MyTable] ([CustomerNumber], [AAA_Value], [BBB_Value], [Name], [Surname]) 
VALUES (N'234567', 30, 40, N'Ross', N'Geller');
GO

——查询

SELECT 
 (
    SELECT 'ABCD' AS [@Name]
          ,'AAA' AS [Data/@DataElement]
          ,AAA_Value AS [Data/@Value]
          ,''
          ,'BBB' AS [Data/@DataElement]
          ,BBB_Value AS [Data/@Value]
    FOR XML PATH('DataSet'),TYPE
 )
 ,''
 ,(
    SELECT 'EFGH' AS [@Name]
          ,'CCC' AS [Data/@DataElement]
          ,Name AS [Data/@Value]
          ,''
          ,'DDD' AS [Data/@DataElement]
          ,Surname AS [Data/@Value]
    FOR XML PATH('DataSet'),TYPE
 )
FROM MyTable
FOR XML PATH('ProData')
GO

——用真实的数据清理注意力!!

--DROP TABLE MyTable;

结果

<ProData>
  <DataSet Name="ABCD">
    <Data DataElement="AAA" Value="10" />
    <Data DataElement="BBB" Value="20" />
  </DataSet>
  <DataSet Name="EFGH">
    <Data DataElement="CCC" Value="Phoebe" />
    <Data DataElement="DDD" Value="Buffay" />
  </DataSet>
</ProData>
<ProData>
  <DataSet Name="ABCD">
    <Data DataElement="AAA" Value="30" />
    <Data DataElement="BBB" Value="40" />
  </DataSet>
  <DataSet Name="EFGH">
    <Data DataElement="CCC" Value="Ross" />
    <Data DataElement="DDD" Value="Geller" />
  </DataSet>
</ProData>

注意:中间的空列(,'')告诉引擎启动一个新元素。