Getting Started with ASP.NET MVC - Part 5: How to do programming with razor syntax
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
XQuery 69
TSQL 67
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
August 2009 19
June 2009 19
May 2010 18
January 2009 15
July 2008 15
January 2010 14
October 2008 14
February 2010 12

Jacob's Blog

FOR XML EXPLICIT Tutorial – Part 3

Dec 14 2007 8:47AM by Jacob Sebastian   

Continued from FOR XML EXPLICIT Tutorial - 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>

 
  • FOR XML EXPLICIT Tutorial- Part 1
  • FOR XML EXPLICIT Tutorial - Part 2
  • FOR XML EXPLICIT Tutorial – Part 3
  • FOR XML EXPLICIT Tutorial – Part 4
  • FOR XML Tutorials

    • Tags: XML, FOR_XML, FOR-XML-EXPLICIT,

    Jacob Sebastian
    1 · 100% · 22473
    0
    Liked
     
    0
    Lifesaver
     
    0
    Refreshed
     
    0
    Learned
     
    0
    Incorrect


    Submit

    Your Comment
    Sign Up or Login to post a comment.
        Copyright © Beyondrelational.com Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising