[翻译]SQL Server And XML: FOR XML EXPLICIT - Part 3

Part 1

Part 2

修改了关于排序的问题以后，让我们继续完善我们的代码。我们在AddressCollection节点下加入名为Addresses的子元素，来完成最终的版本。我们需要新的一级，Tag 4。注意，我用AgentID * 102来确保记录在每个Agent的AddressCollection下边是正确的。

 

SELECT Tag, Parent,
    [Agents!1!],
    [Agent!2!AgentID],
    [Agent!2!Fname!Element],
    [Agent!2!SSN!Element],
    [AddressCollection!3!Element],
    [Address!4!AddressType!Element],
    [Address!4!Address1!Element],
    [Address!4!Address2!Element],
    [Address!4!City!Element]
FROM (
    SELECT
        1 AS Tag,
        NULL AS Parent,
        0 AS Sort,
        NULL AS 'Agents!1!',
        NULL AS 'Agent!2!AgentID',
        NULL AS 'Agent!2!Fname!Element',
        NULL AS 'Agent!2!SSN!Element',
        NULL AS 'AddressCollection!3!Element',
        NULL AS 'Address!4!AddressType!Element',
        NULL AS 'Address!4!Address1!Element',
        NULL AS 'Address!4!Address2!Element',
        NULL AS 'Address!4!City!Element'
    UNION ALL
    SELECT
        2 AS Tag,
        1 AS Parent,
        AgentID * 100,
        NULL, AgentID, Fname, SSN,
        NULL,NULL, NULL, NULL, NULL
        FROM @Agent
    UNION ALL
    SELECT
        3 AS Tag,
        2 AS Parent,
        AgentID * 100 + 1,
        NULL,NULL,NULL, NULL,
        NULL, NULL, NULL, NULL, NULL
    FROM @Agent
    UNION ALL
    SELECT
        4 AS Tag,
        3 AS Parent,
        AgentID * 100 + 2,
        NULL,NULL,NULL,NULL,NULL,
        AddressType, Address1, Address2, City
    FROM @Address
) A
ORDER BY Sort

FOR XML EXPLICIT

 

最终的完整代码如下：

/*
Borrowed from Kent's code
*/

declare @agent table
(
    AgentID int,
    Fname varchar(5),
    SSN varchar(11)
)

insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'

declare @address table
(
    AddressID int,
    AddressType varchar(12),
    Address1 varchar(20),
    Address2 varchar(20),
    City varchar(25),
    AgentID int
)

insert into @address
select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all
select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all
select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all
select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all
select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all
select 6, 'Home', 'ttt', 'loik road', 'NY', 3

/*
End Borrow
*/

SELECT Tag, Parent,
    [Agents!1!],
    [Agent!2!AgentID],
    [Agent!2!Fname!Element],
    [Agent!2!SSN!Element],
    [AddressCollection!3!Element],
    [Address!4!AddressType!Element],
    [Address!4!Address1!Element],
    [Address!4!Address2!Element],
    [Address!4!City!Element]
FROM (
    SELECT
        1 AS Tag,
        NULL AS Parent,
        0 AS Sort,
        NULL AS 'Agents!1!',
        NULL AS 'Agent!2!AgentID',
        NULL AS 'Agent!2!Fname!Element',
        NULL AS 'Agent!2!SSN!Element',
        NULL AS 'AddressCollection!3!Element',
        NULL AS 'Address!4!AddressType!Element',
        NULL AS 'Address!4!Address1!Element',
        NULL AS 'Address!4!Address2!Element',
        NULL AS 'Address!4!City!Element'
    UNION ALL
    SELECT
        2 AS Tag,
        1 AS Parent,
        AgentID * 100,
        NULL, AgentID, Fname, SSN,
        NULL,NULL, NULL, NULL, NULL
        FROM @Agent
    UNION ALL
    SELECT
        3 AS Tag,
        2 AS Parent,
        AgentID * 100 + 1,
        NULL,NULL,NULL, NULL,
        NULL, NULL, NULL, NULL, NULL
    FROM @Agent
    UNION ALL
    SELECT
        4 AS Tag,
        3 AS Parent,
        AgentID * 100 + 2,
        NULL,NULL,NULL,NULL,NULL,
        AddressType, Address1, Address2, City
    FROM @Address
) A
ORDER BY Sort

FOR XML EXPLICIT

 

/*

OUTPUT:

<Agents>

    <Agent AgentID="1">

        <Fname>Vimal</Fname>

        <SSN>123-23-4521</SSN>

        <AddressCollection>

            <Address>

                <AddressType>Home</AddressType>

                <Address1>abc</Address1>

                <Address2>xyz road</Address2>

                <City>RJ</City>

            </Address>

            <Address>

                <AddressType>Office</AddressType>

                <Address1>temp</Address1>

                <Address2>ppp road</Address2>

                <City>RJ</City>

            </Address>

        </AddressCollection>

    </Agent>

    <Agent AgentID="2">

        <Fname>Jacob</Fname>

        <SSN>321-52-4562</SSN>

        <AddressCollection>

            <Address>

                <AddressType>Home</AddressType>

                <Address1>xxx</Address1>

                <Address2>aaa road</Address2>

                <City>NY</City>

            </Address>

            <Address>

                <AddressType>Office</AddressType>

                <Address1>ccc</Address1>

                <Address2>oli Com</Address2>

                <City>CL</City>

            </Address>

            <Address>

                <AddressType>Temp</AddressType>

                <Address1>eee</Address1>

                <Address2>olkiu road</Address2>

                <City>CL</City>

            </Address>

        </AddressCollection>

    </Agent>

    <Agent AgentID="3">

        <Fname>Tom</Fname>

        <SSN>252-52-4563</SSN>

        <AddressCollection>

            <Address>

                <AddressType>Home</AddressType>

                <Address1>ttt</Address1>

                <Address2>loik road</Address2>

                <City>NY</City>

            </Address>

        </AddressCollection>

    </Agent>

</Agents>

*/