从 SQL 查询创建 XML 文档

Question

我希望您能够从SQL 2005查询中创建以下XML文档。 我遇到的问题是该文档具有相同级别的3个节点 - 客户，维修设施和操作。

--所需内容

<Elements>
    <Element>
        <Customer>
            <FirstName></FirstName>
            <LastName></LastName>
        </Customer>
        <RepairFacility>
          <LocationName></LocationName>
          <LocationPhone></LocationPhone>
        </RepairFacility>
        <Action>
          <FollowUpFlag></FollowUpFlag>
          <DateAction></DateAction>
        </Action>
    </Element>
    <Element>
    </Element>
</Elements>

--我的查询

SELECT
  (  SELECT .....
FROM    tbl A1
FOR  XML PATH('Customer'),  TYPE ),
  ( SELECT  ......
FROM tbl        A2
FOR  XML PATH('RepairFacility'), TYPE),
  ( SELECT .....                             
FROM tbl J
FOR  XML PATH('Action'),     TYPE    )
FOR XML PATH(''), ROOT('Element')

--我得到了什么

<Elements>
    <Element>
        <Customer>
            <FirstName></FirstName>
            <LastName></LastName>
        </Customer>
        <Customer>
            <FirstName></FirstName>
            <LastName></LastName>
        </Customer>
        <RepairFacility>
          <LocationName></LocationName>
          <LocationPhone></LocationPhone>
       </RepairFacility>
       <RepairFacility>
          <LocationName></LocationName>
          <LocationPhone></LocationPhone>
       </RepairFacility>
       <Action>
          <FollowUpFlag></FollowUpFlag>
          <DateAction></DateAction>
       </Action>
       <Action>
          <FollowUpFlag></FollowUpFlag>
          <DateAction></DateAction>
       </Action>
    </Element>
    <Element>
</Elements>

我非常感谢任何帮助。

- Mark P

Answer 1

试试这个：

-- Query
SELECT  (
    SELECT  A1.FirstName
        ,   A1.LastName
    FROM    #Customer AS A1
    WHERE   A1.ID = Z.CustomerID
    FOR  XML PATH('Customer') ,  TYPE
),
        (
    SELECT  A2.LocationName
        ,   A2.LocationPhone
    FROM    #RepairFacility AS A2
    WHERE   A2.ID = Z.RepairFacilityID
    FOR XML PATH('RepairFacility') , TYPE
),
        (
    SELECT  A3.FollowUpFlag
        ,   A3.DateAction
    FROM    #Action AS A3
    WHERE   A3.ID = Z.ActionID
    FOR XML PATH('Action') , TYPE
)
FROM    (
    SELECT  A1.ID   AS CustomerID
        ,   A2.ID   AS RepairFacilityID
        ,   A3.ID   AS ActionID
    FROM    #Action A3
       JOIN #Customer A1 ON (A3.CustomerID = A1.ID)
       JOIN #RepairFacility A2 ON (A3.RepairFacilityID = A2.ID)
) AS Z
FOR XML PATH('Element'), ROOT('Elements') 

这是一些样本数据：

-- Sample data
CREATE TABLE #Customer (
        ID              int             IDENTITY
    ,   FirstName       varchar(50)
    ,   LastName        varchar(50)
)

CREATE TABLE #RepairFacility (
        ID              int             IDENTITY
    ,   LocationName    varchar(50)
    ,   LocationPhone   varchar(50)
)

CREATE TABLE #Action (
        ID                  int         IDENTITY
    ,   CustomerID          int
    ,   RepairFacilityID    int
    ,   FollowUpFlag        bit
    ,   DateAction          datetime
)

INSERT #Customer (FirstName, LastName) VALUES ('John', 'Smith')
INSERT #RepairFacility (LocationName, LocationPhone) VALUES ('New York', '[phone]')
INSERT #Action (CustomerID, RepairFacilityID, FollowUpFlag, DateAction) VALUES (1, 1, 0, GETDATE())

INSERT #Customer (FirstName, LastName) VALUES ('Jane', 'Doe')
INSERT #RepairFacility (LocationName, LocationPhone) VALUES ('Chicago', '[phone]')
INSERT #Action (CustomerID, RepairFacilityID, FollowUpFlag, DateAction) VALUES (2, 2, 1, GETDATE())

此查询具有以下输出：

<Elements>
  <Element>
    <Customer>
      <FirstName>John</FirstName>
      <LastName>Smith</LastName>
    </Customer>
    <RepairFacility>
      <LocationName>New York</LocationName>
      <LocationPhone>[phone]</LocationPhone>
    </RepairFacility>
    <Action>
      <FollowUpFlag>0</FollowUpFlag>
      <DateAction>2012-03-22T08:33:08.617</DateAction>
    </Action>
  </Element>
  <Element>
    <Customer>
      <FirstName>Jane</FirstName>
      <LastName>Doe</LastName>
    </Customer>
    <RepairFacility>
      <LocationName>Chicago</LocationName>
      <LocationPhone>[phone]</LocationPhone>
    </RepairFacility>
    <Action>
      <FollowUpFlag>1</FollowUpFlag>
      <DateAction>2012-03-22T08:41:35.640</DateAction>
    </Action>
  </Element>
</Elements>

- Ryan