Post reply

Selecting XML out

  • October 1, 2003 at 12:22 am

    #82607

    Greetings.

    I have a table with multiple rows for Description belonging to one Heading. For each row of data I need a <DESCRIPTION LINE="n">.

    The xml should look like this:

    - <FREEFORMENDORSEMENT-EW502>

    <HEADING>A heading</HEADING>

    <DESCRIPTION LINE="1">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="2">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="3">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="4">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="5">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="6">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="7">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="8">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="9">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="10">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="11">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="12">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="13">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="14">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    <DESCRIPTION LINE="15">123456789012345678901234567890123456789012345678901234567890</DESCRIPTION>

    ...

    </FREEFORMENDORSEMENT-EW502>

    Does anyone know of a way to get this result?

    Any help will be greatly appreciated.

    Thanx

  • October 1, 2003 at 2:03 am

    #476495

    select 1 as tag, null as parent,

    'whatever' as [FREEFORMENDORSEMENT-EW502!1!attrib],

    null as [HEADING!2!!element],

    null as [DESCRIPTION!3!line],

    null as [DESCRIPTION!3!!element]

    union all

    select 2, 1, null, [heading], null , null from #whatever#

    union all

    select 3, 1, null, null, [lineno] , [desc text] from #whatever#

    order by #use ordering to make sure your nesting works#

    for xml auto

    HTH

    Keith Henry

    DBA/Developer/BI Manager

    Edited by - keithh on 10/01/2003 02:03:20 AM




    Keith Henry


    According to everyone I know I "do something with computers?" for a living, so there you go.

  • October 1, 2003 at 4:19 pm

    #476653

    Hey Keith,

    Wouldn't you want to use FOR XML EXPLICIT with your sql rather than AUTO?

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • October 2, 2003 at 8:33 am

    #476784

    Doh! yes.

    Stags sent me an email and I helped him out directly in the end

    Keith Henry

    DBA/Developer/BI Manager




    Keith Henry


    According to everyone I know I "do something with computers?" for a living, so there you go.

  • October 3, 2003 at 2:53 am

    #476927

    Greetings

    I managed to get it working with the help of Keith (Thanks Keith). I'm sorry for going directly to Keith and bypassing the Forum, this was my first post and it won't happen again. I realise that it defeats the purpose of the forum. I have uploaded the script (Titled Selecting XML out) for future reference.

    Thanks & sorry once again.

Viewing 5 posts - 1 through 4 (of 4 total)

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