Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: Selecting into XML format

    Hi there

    I have a few tables in MSSQL Server 2008 from which I need to select data into xml format.

    The structure for the result XML is as follows:

    PersonAccount
    .........Address
    .........EmployeeProfile
    .............Address
    .........GuestProfile
    .............Address
    .........StudentProfile
    .............Address

    So each person account has an adress together with 3 different profiles, all with addresses coming from the same table address.

    Tables PersonAccount, EmployeeProfile, GuestProfile and StudentProfile are linked via PersonID and all 4 tables contain an AddressID linking to table Address.

    Can anyone help me to do a select on these tables to get the xml structure above?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    FOR XML

    Do you have SQL Server Books Online (BOL)???

    What's New in FOR XML in Microsoft SQL Server 2005
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Selecting into XML format

    Here's a quick SELECT framework you can use...

    Code:
    select pa.*
     ,(select * from Address where AddressID = pa.AddressID for xml path('Address') ,type) -- PA Address
    
     ,(select pf.* ,(select * from Address where AddressID = pf.AddressID for xml path('Address') ,type) -- EP Address
       from EmployeeProfile pf (nolock)
       where pf.PersonID = pa.PersonID
       for xml path('EmployeeProfile') ,type
      ) -- EmployeeProfile
    
     ,(select pf.* ,(select * from Address where AddressID = pf.AddressID for xml path('Address') ,type) -- GP Address
       from GuestProfile pf (nolock)
       where pf.PersonID = pa.PersonID
       for xml path('GuestProfile') ,type
      ) -- GuestProfile
    
     ,(select pf.* ,(select * from Address where AddressID = pf.AddressID for xml path('Address') ,type) -- SP Address
       from StudentProfile pf (nolock)
       where pf.PersonID = pa.PersonID
       for xml path('StudentProfile') ,type
      ) -- StudentProfile
    from PersonAccount pa (nolock)
    for xml path('PersonAccount') ,root('PersonAccounts') ,type

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •