Post reply

Exporting data to XML

  • February 26, 2008 at 3:25 am

    #186932

    Hi Guys,

    I have been successful in importing xml files into sql server2005, but can't seem to come right with exporting.

    I got 2 tables (batch and prospect) which i need to query to produce my xml file. The problem im having is trying to get the result in the required layout.

    Below is an example of my query, the output result and the required result i need.

    Would really appreciate any help I get....

    Thanks!!!

    --Query

    select

    1 as tag,

    0 as parent,

    externalid as [prospectbatch!1!externalid],

    b.ititid as [prospectbatch!1!ititid],

    containstype as [prospectbatch!1!containstype],

    null as [prospect!2!recordid],

    null as [prospect!2!ititid],

    null as [name!3!name],

    null as [surname!4!surname],

    null as [idnumber!5!idnumber]

    from batch b

    union all

    select

    2 as tag,

    1 as parent,

    isnull(externalid,''),

    isnull(b.ititid,''),

    isnull(containstype,''),

    isnull(p.recordid,'') ,

    isnull(p.ititid,''),

    null,null,null

    from batch b,prospect p

    where b.internalbatch = p.internalbatch

    union all

    select

    3 as tag,

    2 as parent,

    null,null,null,

    isnull(recordid,'') ,

    isnull(ititid,''),

    isnull([name],''),

    null,null

    from prospect p

    union all

    select

    4 as tag,

    2 as parent,

    null,null,null,

    isnull(recordid,'') ,

    isnull(ititid,''),

    isnull([name],''),

    isnull(surname,''),

    null

    from prospect p

    union all

    select

    5 as tag,

    2 as parent,

    null,null,null,

    isnull(recordid,'') ,

    isnull(ititid,''),

    isnull([name],''),

    isnull(surname,''),

    isnull(idnumber,'')

    from prospect p

    for xml explicit

    --Output Result

    --Required XML Result

  • February 26, 2008 at 4:07 am

    #781905

    Sorry guys, please find attached my current output result and the required result..Thanks

  • February 26, 2008 at 4:19 am

    #781909

    Easier to use FOR XML PATH

    select b.externalid as "@externalid",

    b.ititid as "@ititid",

    b.containstype as "@containstype",

    (select p.recordid as "@recordid",

    p.ititid as "@ititid",

    p.[name] as "name",

    p.surname as "surname",

    p.idnumber as "idnumber"

    from prospect p

    where b.internalbatch = p.internalbatch

    for xml path('prospect'),type)

    from batch b

    for xml path('prospectbatch'),root('xml'),type

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 26, 2008 at 4:33 am

    #781913

    Awesome!!! Thanks Mark!!

    I've added all my fields in and it works perfect..thanks

    Do u know of any good sites or books i can read up on, to get a better understanding of working with xml on sql server2005

  • February 26, 2008 at 4:45 am

    #781917

    Read these:

    http://www.developer.com/db/article.php/3531196

    http://www.15seconds.com/issue/050803.htm

    http://blogs.msdn.com/mrorke/archive/2005/07/21/441554.aspx

    http://www.devx.com/xml/Article/26766

    http://msdn2.microsoft.com/en-us/library/ms345117.aspx

    They may help you.

    🙂

  • February 26, 2008 at 5:17 am

    #781929

    Thanks Anirban....

  • February 28, 2008 at 1:56 am

    #783043

    hi Guys, how do I export my xml result directly to a xml file.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply