Problem in FOR XML Explicit Query

Question

 

Greetings,

 

I am using the FOR XML Explicit for fetching such kind of XML Output.

 

<Agents>

<Agent AgentID="1">

<Fname>abc</Fname>

<SSN>143-341-111</SSN>

<AddressCollection>

<Address>

<AddressType>Home</AddressType>

<Address1>hhh</Address1>

<Address2>oooo</Address2>

<City>rlll</City>

</Address>

 

<Address>

<AddressType>Office</AddressType>

<Address1>SSS</Address1>

<Address2>ppp</Address2>

<City>eufjjf</City>

</Address>

 

</AddressCollection>

</Agent>

 

<Agent AgentID="2">

<Fname>bbb</Fname>

<SSN>263-347-231</SSN>

<AddressCollection>

<Address>

<AddressType>Office</AddressType>

<Address1>sfdf</Address1>

<Address2>popo</Address2>

<City>lrekrj</City>

</Address>

<AddressCollection>

 

</Agent>

</Agents>

 

 

I have a two tables Agent and Address. Both containts AgentID field based on that i am trying to fetch Such result.

 

In Agent Table  i have 

- Agent ID

- Fname

-SSN

In Address Table I have

- Address Type

- Address1

-Address2

- City

I have tried it using FOR XML Explicit,  but not abl to get correct out put... Some of fields are not coming perfectly...

 

Here i have attached the code...

 

 

SELECT distinct

1 AS TAG,

NULL AS Parent,

NULL AS [Agents!1!Agent!Element],

agenttemp.agentid AS [Agent!2!AgentID],

NULL AS [Agent!2!Fname!Element],

NULL AS [Agent!2!SSN!Element],

NULL AS [Agent!2!AddressCollection!Element],

NULL AS [AddressCollection!3!Address!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]

from agenttemp

union all

 

select

2 AS TAG,

1 AS Parent,

NULL AS [Agents!1!Agent!Element],

agenttemp.agentid AS [Agent!2!AgentID],

Fname AS [Agent!2!Fname!Element],

SSN AS [Agent!2!SSN!Element],

NULL AS [Agent!2!AddressCollection!Element],

NULL AS [AddressCollection!3!Address!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]

from agenttemp

union all

select

4 as TAG,

3 AS Parent,

NULL AS [Agents!1!Agent!Element],

agenttemp.AgentID as [Agent!2!AgentID],

agenttemp.Fname AS [Agent!2!Fname!Element],

agenttemp.SSN AS [Agent!2!SSN!Element],

NULL AS [Agent!2!AddressCollection!Element],

NULL AS [AddressCollection!3!Address!Element],

AddressType AS [Address!4!AddressType!Element],

Address1 AS [Address!4!Address1!Element],

Address2 AS [Address!4!Address2!Element],

City AS [Address!4!City!Element]

from agenttemp inner join addresstemp on

agenttemp.agentid = addresstemp.agentid

 

union all

 

 

 

SELECT

3 AS TAG,

2 AS Parent,

NULL AS [Agents!1!Agent!Element],

agenttemp.agentid AS [Agent!2!AgentID],

Fname AS [Agent!2!Fname!Element],

SSN AS [Agent!2!SSN!Element],

NULL AS [Agent!2!AddressCollection!Element],

NULL AS [AddressCollection!3!Address!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]

from agenttemp

 

order by agenttemp.agentid

for xml explicit

 

 

 

Please help me in this.....

Waiting for your reply...

 

Thanking you..

 

Vimal

Answer 1

Hi vimal,
Please post the scripts to create the tables and populate them with some sample data.

Jacob

Answer 2

Hi Jacob,

 

 

The Scrip for the Creating a Agent Table is :

 

create table Agent(AgentID INT IDENTITY PRIMARY KEY, FName VARCHAR(25), SSN VARCHAR (20))

 

And Scrip for the Creating a Address Table is :

 

create table AddressM (AddressID INT IDENTITY PRIMARY KEY, AddressType VARCHAR(25), Address1 VARCHAR(25), Address2 VARCHAR(25), City VARCHAR(25),AgentID INT FOREIGN KEY REFERENCES AgentM(AgentID))

 

 

------

 

Some Sample Data Like...

 

In Agent Table

 

AgentID     Fname     SSN

1               Vimal      123-23-4521

2               Jacob      321-52-4562

3               Tom        251-52-4563

 

In Address Table

 

AddressID     AddressType    Address1      Address2        City       AgentID

1                  Home              abc               xyz road          RJ        1

2                  Office              temp             ppp road          RJ        1

3                  Home              xxx               aaa road          NY       2

4                  Office              ccc               oli Com.           CL       2

5                  Temp              eee               olkiu road        CL        2

6                  Home              ttt                 loik  road          NY        3

 

 

Above are the Sample data which can be used..

I hope this will be sufficient info.

 

Hey Jacob, I am thinking that, the problem might be with the dynamic  tree structure of XML, beacuse in AddressCollection Node, number of address are not fixed.

 

I have try to solve this with different way, but i was not abl to.. Some time it giving me an error.

 

Msg 6833, Level 16, State 1, Line 1

Parent tag ID 3 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

 

 

But the above query is working fine...

 

Thanking you...

 

Regards,

Vimal

        

Answer 3

The problem could be in two places.

1. in the sort order of the result set.

2. in the construction of the tags.

 

I have the scripts for the table. but i dont have the script for the sample data. If you can send me the insert statements for some data and your query which gives error, probably i could help.

 

BTW:there is another thread which had problem #2 mentiond above. you should probably have a look:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2473587&SiteID=1

The problem was with the way the tags were constructed.

 

Answer 4

Perhaps something like?

 

Code Block

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'
--select * from @agent

 

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
--select * from @address

 

select
  AgentID as[@AgentID],
  Fname as [Fname],
  ssn as [SSN]
 ,( select
      AddressType as [Address/AddressType],
      Address1 as [Address/Address1],
      Address2 as [Address/Address2],
      City as [Address/City]
    from @address b
    where a.AgentID = b.AgentID
    for xml path(''), type
  ) as [AddressCollection]
from @agent a
for xml path('Agent'), Root('Agents')

 

/* -------- Sample Output: --------
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-----------------------------------------------
<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>
*/

 

 

 

Answer 5

Hi,

 

You place the 'SELECT 4 AS TAG, 3 AS Parent, ...' before

the 'SELECT 3 AS TAG, 2 AS Parent, ...'.

 

But, a parents' definitions must be located above a children's definitions.

 

Try to replace them.

 

Regards.

Answer 6

Greetings,

 

First of all i would like to thank Kent... Your solution work perfectly fine. But it only works for the SQL Server 2005.

In SQL Server it's not working... SQL Server 2000 doesn't support the PATH Mode. I have posted the FOR XML EXPLICIT Query written in SQL Server 2000 in the 1st post. It's not showing perfect result.. Can we get the Same output of SQL Server 2005, in SQL Server 2000 With FOR XML EXPLICIT Query...

 

Waiting for your reply..

Thanking you..

 

Regards,

Vimal

Answer 7

Hi Lev,

 

I have changed the code as per your guiding.. But still it's not giving me perfect result. It generates the Error.

 

Msg 6833, Level 16, State 1, Line 1

Parent tag ID 3 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

 

The modified code is below.

 

 

SELECT distinct

1 AS TAG,

NULL AS Parent,

NULL AS [Agents!1!Agent!Element],

agenttemp.agentid AS [Agent!2!AgentID],

NULL AS [Agent!2!Fname!Element],

NULL AS [Agent!2!SSN!Element],

NULL AS [Agent!2!AddressCollection!Element],

NULL AS [AddressCollection!3!Address!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]

from agenttemp

union all

select

2 AS TAG,

1 AS Parent,

NULL AS [Agents!1!Agent!Element],

agenttemp.agentid AS [Agent!2!AgentID],

Fname AS [Agent!2!Fname!Element],

SSN AS [Agent!2!SSN!Element],

NULL AS [Agent!2!AddressCollection!Element],

NULL AS [AddressCollection!3!Address!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]

from agenttemp

union all

 

 

SELECT

3 AS TAG,

2 AS Parent,

NULL AS [Agents!1!Agent!Element],

agenttemp.agentid AS [Agent!2!AgentID],

Fname AS [Agent!2!Fname!Element],

SSN AS [Agent!2!SSN!Element],

NULL AS [Agent!2!AddressCollection!Element],

NULL AS [AddressCollection!3!Address!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]

from agenttemp

union all

select

4 as TAG,

3 AS Parent,

NULL AS [Agents!1!Agent!Element],

agenttemp.AgentID as [Agent!2!AgentID],

agenttemp.Fname AS [Agent!2!Fname!Element],

agenttemp.SSN AS [Agent!2!SSN!Element],

NULL AS [Agent!2!AddressCollection!Element],

NULL AS [AddressCollection!3!Address!Element],

AddressType AS [Address!4!AddressType!Element],

Address1 AS [Address!4!Address1!Element],

Address2 AS [Address!4!Address2!Element],

City AS [Address!4!City!Element]

from agenttemp inner join addresstemp on

agenttemp.agentid = addresstemp.agentid

order by agenttemp.agentid

for xml explicit

 

 

Thanking you...

Waiting for your reply..

 

Regards,

Vimal

Answer 8

This version with EXPLICIT works in SQL Server 2000

 

Code Block

/*

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>

*/

 

 

Answer 9

Hi Jacob,

 

Good work man.. It's working fine for the table variable.. But when i am trying to execute the FOR XML EXPLICIT Query for the table stored in database.. It's giving me an ERROR :

 

Msg 6833, Level 16, State 1, Line 1

Parent tag ID 3 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

 

 

I mean, while executing FOR XML EXPLICIT Query, i am using the Agent table stored in my database instead of @Agent  (Variable table).

Same for the Address table. So Please guide me.. how to solve this error. ?

 

And Jacob, one more thing man. I didn't understand the purpose of writting..

 

AgentID * 100, Agent * 100 + 1,  Agent * 100 + 2

 

 

Please guide me..

 

Waiting for your reply...

 

Thanking you.

 

Regards,

Vimal

Answer 10

AgentID * 100 is used to generate a sort order at which i will sort the data before passing it to EXPLICIT. EXPLICIT will generate data in the same order as you pass the result set into it. Hence it is important that you give the data in correct order. I wanted to make sure that the address of agentID = 1 will appear right after that record and then agent2 will appear with its address.

Just run the query without 'for xml explicit' and you will see the results.

If you refer to one of my replies earlier in this thread, i had mentiond that the problem might be with the order of the data. Hence I had asked you for the scripts to insert data to my sample table. If i can get the insert queries, i can run it and see what is the problem.

What you could do is to run your query without 'for xml explicit' and see if you see the correct hierarchy in the result set. This might give you some hint if there is a problem with the sort order.

regards
Jacob

Answer 11

Hi Jacob,

 

 

In your code, i have made some changes and it's woking fine... At run time i am fetching the  data of Agent and Address table in @Agent and @Address local variable.  And it's working fine man... But i need more

Clarification  about  the Query...  Below i have written the your query with my modification...

 

 

 

declare @agent table

(

AgentID int,

Fname varchar(25),

SSN varchar(25)

)

insert into @agent

// start  modified Parth  - 1

select * from agent

//end

declare @address table

(

AddressID int,

AddressType varchar(25),

Address1 varchar(25),

Address2 varchar(25),

City varchar(25),

AgentID int

)

insert into @address

// start  modified Parth  - 2

select * from address

// end

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

 

Need to understand...

 

 1. Without comparing the Agent ID of @Address  and @Agent how can particular address come under the right Agent..

 

2. If possible please guide me the whole flow you have used in Inner part...

 

Waiting for your reply..

 

Thanking you..

 

Regards,

Vimal

 

 

Answer 12

vimal,
I am trying to write down the steps I went through while writing the above code. This will give you a clear explanation of your questions. I will upload that later today.

Jacob

Answer 13

http://jacobsebastian.blogspot.com/2007/12/for-xml-explicit-part-1.html

Vimal,

I have written a long post which explains the flow as well as the steps followed to write the above sample code. You can find the post at

 

Hope this helps

Jacob

Answer 14

http://jacobsebastian.blogspot.com/2007/12/for-xml-explicit-part-2.html

Hi all,

 

I remembered a little... Jacob's analytic tables () were helped me

 

This is works also:

Code Block

SELECT

       1 AS Tag,

       NULL AS Parent,

       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,

       NULL, AgentID, Fname, SSN,

       NULL,NULL, NULL, NULL, NULL

  FROM @Agent

 

UNION ALL

 

SELECT

       3 AS Tag,

       2 AS Parent,

       NULL,AgentID,NULL, NULL,

       NULL, NULL, NULL, NULL, NULL

 

  FROM @Agent

 

UNION ALL

 

SELECT

       4 AS Tag,

       3 AS Parent,

       NULL,AgentID,NULL,NULL,NULL,

       AddressType, Address1, Address2, City

 

  FROM @Address

 

 ORDER BY

       -- all properties of every agent

       -- (from tag 2 and 4: SSN, fname and adresses)

       -- will be sorted by agentID and combined into

       -- separate groups. It is necessary in same cases.

       [Agent!2!AgentID],

       [AddressCollection!3!Element], -- optional because NULL everywhere

       [Address!4!AddressType!Element]-- any ordering by elements of tag 4

 

FOR XML EXPLICIT

 

 

 

Regards,

Lev.

 

Answer 15

Greetings,

 

Thanks Jacob for providing me a very good solution with excellent explanation. You guys ( Jacob,  Kent,  Lev) helped me a lot in my problem and provided  perfect solution. I am really thankful to  all guys ( Jacob,  Kent,  Lev) . I am also thankful to this forum for providing such a good technical supporter. 

Hey guys, please let me know if i can help you in your problems.

 

Thanking you...

 

 

Regards,

Vimal Rughani

 

 

Answer 16

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
    1 AS Tag,
    NULL AS Parent,
    0 AS 'Sort!10!',-- (Sort!A number bigger than 4!)
    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
ORDER BY 'Sort!10!'

FOR XML EXPLICIT

 

Answer 17

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
    1 AS Tag,
    NULL AS Parent,
    0 AS 'Sort!10!',-- (Sort!A number bigger than 4!)
    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
ORDER BY 'Sort!10!'

FOR XML EXPLICIT