create XML doc from SQL query

Question

I'm trying to create the below XML doc from a sql 2005 query. What I'm struglling with is the doc has 3 nodes on the same level - Customer, RepairFacility, and Action.

--What is needed

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

--My Query

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

--What I get

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

I'm grateful for any help.

Answer 1

Try this:

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

Here is a little sample data:

-- 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', '(123) 555-1234')
INSERT #Action (CustomerID, RepairFacilityID, FollowUpFlag, DateAction) VALUES (1, 1, 0, GETDATE())

INSERT #Customer (FirstName, LastName) VALUES ('Jane', 'Doe')
INSERT #RepairFacility (LocationName, LocationPhone) VALUES ('Chicago', '(789) 555-7890')
INSERT #Action (CustomerID, RepairFacilityID, FollowUpFlag, DateAction) VALUES (2, 2, 1, GETDATE())

This query has the following output:

<Elements>
  <Element>
    <Customer>
      <FirstName>John</FirstName>
      <LastName>Smith</LastName>
    </Customer>
    <RepairFacility>
      <LocationName>New York</LocationName>
      <LocationPhone>(123) 555-1234</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>(789) 555-7890</LocationPhone>
    </RepairFacility>
    <Action>
      <FollowUpFlag>1</FollowUpFlag>
      <DateAction>2012-03-22T08:41:35.640</DateAction>
    </Action>
  </Element>
</Elements>