Google
 

Friday, December 14, 2007

FOR XML EXPLICIT - Part 3

Part 1

Part 2

Having fixed the problem with the sort order, let us go ahead with the rest of the code. Let us add Addresses under the AddressCollection node and come up with the final version of the code. We need to add a new level, Tag 4. Note that I used AgentID * 102 to make sure that this record will come right below the AddressCollection row of each Agent.

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


Here is the complete listing of the code.

/*

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>

*/

0 comments:

My Favorite Books