时间戳

首页 > 解决方案 > 来自 TSQL 中表的 XML 具有不同的属性值

sql - 来自 TSQL 中表的 XML 具有不同的属性值

问题描述

我需要下面提供的以下两个表中的 XML。我遇到的问题是我无法在 XML 中分离出每个项目的属性。

declare @A table
(
  PROJECT_ID varchar(10), 
  SITE_ID varchar(10)
);

declare @B table
(
    PROJECT_ID varchar(10),
    ATTRIBUTE_NAME varchar(15)
)

insert into @A values
('PROJECT1', 'A'),
('PROJECT2', 'B');

insert into @B values
('PROJECT1', 'PERSONID'),
('PROJECT1', 'GIVENNAME'),
('PROJECT1', 'MIDDLENAME'),
('PROJECT1', 'FAMILYNAME'),
('PROJECT1', 'DATEOFBIRTH'),
('PROJECT1', 'SEX'),
('PROJECT2', 'PERSON_ID'),
('PROJECT2', 'GIVEN_NAME'),
('PROJECT2', 'MIDDLE_NAME'),
('PROJECT2', 'FAMILY_NAME'),
('PROJECT2', 'DATEOF_BIRTH');

我想要的 XML 结果是：

<SITEID>A</SITEID>
<Project>
    <Name>PROJECT 1</Name>
    <Columns>
        <PracticeColumn>
            <Name>PERSONID</Name>           
        </PracticeColumn>
        <PracticeColumn>
            <Name>GIVENNAME</Name>      
        </PracticeColumn>
        <PracticeColumn>
            <Name>MIDDLENAME</Name>     
        </PracticeColumn>
        <PracticeColumn>
            <Name>FAMILYNAME</Name>     
        </PracticeColumn>
        <PracticeColumn>
            <Name>DATEOFBIRTH</Name>        
        </PracticeColumn>
        <PracticeColumn>
            <Name>SEX</Name>        
        </PracticeColumn>       
    </Columns>
</Project>
<Project>
    <Name>PROJECT 2</Name>
    <Columns>
        <PracticeColumn>
            <Name>PERSON_ID</Name>          
        </PracticeColumn>
        <PracticeColumn>
            <Name>GIVEN_NAME</Name>     
        </PracticeColumn>
        <PracticeColumn>
            <Name>MIDDLE_NAME</Name>        
        </PracticeColumn>
        <PracticeColumn>
            <Name>FAMILY_NAME</Name>        
        </PracticeColumn>
        <PracticeColumn>
            <Name>DATEOF_BIRTH</Name>       
        </PracticeColumn>
    </Columns>
</Project>

注意：每个项目可以有一组不同的属性。所以 XML 必须迎合这一点。

这是我尝试过的：

SELECT a.PROJECT_ID as Name,                    
                (SELECT 
                    ATTRIBUTE_NAME as Name                      
                FROM @A a
                INNER JOIN @B b ON b.PROJECT_ID = a.PROJECT_ID                  
                --WHERE  a.SITE_ID = 'A'                        
                FOR XML PATH ('PracticeColumn'), Root('Columns'),TYPE 
                )
        FROM @A a
        --WHERE  .SITE_ID = 'A'
        FOR XML PATH ('Project'),TYPE

并得到下面的结果。xml 中的每个项目都具有来自所有项目的所有属性。

<Project>
  <Name>PROJECT1</Name>
  <Columns>
    <PracticeColumn>
      <Name>PERSONID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVENNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLENAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILYNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOFBIRTH</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>SEX</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>PERSON_ID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVEN_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLE_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILY_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOF_BIRTH</Name>
    </PracticeColumn>
  </Columns>
</Project>
<Project>
  <Name>PROJECT2</Name>
  <Columns>
    <PracticeColumn>
      <Name>PERSONID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVENNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLENAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILYNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOFBIRTH</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>SEX</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>PERSON_ID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVEN_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLE_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILY_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOF_BIRTH</Name>
    </PracticeColumn>
  </Columns>
</Project>

标签： sqlsql-serverxmltsqlstored-procedures

解决方案

您需要像这里这样的相关子查询。您不加入它们，而是使用适当的 WHERE 子句：

declare @A table
(
  PROJECT_ID varchar(10), 
  SITE_ID varchar(10)
);

declare @B table
(
    PROJECT_ID varchar(10),
    ATTRIBUTE_NAME varchar(15)
)

insert into @A values
('PROJECT1', 'A'),
('PROJECT2', 'B');

insert into @B values
('PROJECT1', 'PERSONID'),
('PROJECT1', 'GIVENNAME'),
('PROJECT1', 'MIDDLENAME'),
('PROJECT1', 'FAMILYNAME'),
('PROJECT1', 'DATEOFBIRTH'),
('PROJECT1', 'SEX'),
('PROJECT2', 'PERSON_ID'),
('PROJECT2', 'GIVEN_NAME'),
('PROJECT2', 'MIDDLE_NAME'),
('PROJECT2', 'FAMILY_NAME'),
('PROJECT2', 'DATEOF_BIRTH');

--查询

SELECT SITE_ID AS SITEID
     ,(
       SELECT a2.PROJECT_ID AS [Name]
             ,(
                SELECT b.ATTRIBUTE_NAME AS [PracticeColumn/Name]
                FROM @B b 
                WHERE b.PROJECT_ID=a2.PROJECT_ID
                FOR XML PATH(''),TYPE
              ) AS [Columns]
       FROM @A a2 
       WHERE a2.SITE_ID=a.SITE_ID
       FOR XML PATH('Project'),TYPE
      )
FROM @A a
GROUP BY SITE_ID
FOR XML PATH('');

结果

<SITEID>A</SITEID>
<Project>
  <Name>PROJECT1</Name>
  <Columns>
    <PracticeColumn>
      <Name>PERSONID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVENNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLENAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILYNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOFBIRTH</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>SEX</Name>
    </PracticeColumn>
  </Columns>
</Project>
<SITEID>B</SITEID>
<Project>
  <Name>PROJECT2</Name>
  <Columns>
    <PracticeColumn>
      <Name>PERSON_ID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVEN_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLE_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILY_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOF_BIRTH</Name>
    </PracticeColumn>
  </Columns>
</Project>

一些备注

我不知道您想如何将其包含<SITEID>到您的 XML 中。从这个角度来看，预期的结果并不完整。所以我使用三个级别的相关子查询。但我认为<SITEID>最好将其包含在<Project>-element 中，或者可能是像 here 这样的 Xml 属性<Project SITEID="A">。您可能还想将一个站点的所有<SITEID>项目分组。这取决于你。

推荐阅读