Post reply

FOR XML PATH question on column data instead of column names

  • May 17, 2010 at 4:29 am

    #98662

    Hi

    I can get my FOR XML stuff to work with using column names as elements or attributes but for the life of me, cannot get the following to work:

    Say for example I have a table as follows:

    Table1

    Col 1 | Col 2

    ----------------

    AttribA | abc

    AttribB | def

    AttribC | wxy

    AttribD | z

    I would like xml to be generated as follows using FOR XML (I do a bit of c# so I can obviously do this with linq2xml but I'd much rather do it from within sql)

    <AttribA>abc</AttribA>

    <AttribB>def</AttribB>

    <AttribC>wxy</AttribC>

    <AttribD>z</AttribD>

    Is this possible with FOR XML at all?

    Thanks!

    Neles

  • May 18, 2010 at 5:03 am

    #1168705

    ok, i got it to work

  • May 18, 2010 at 5:25 am

    #1168716

    Could you please provide the solution

  • May 18, 2010 at 6:51 am

    #1168777

    I would be interested in your solution to this also!

    Thanks!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 20, 2010 at 9:42 am

    #1170239

    I have the same issue and it drives me mad!!

    Could you please provide us with the solution?

    Cheers

    Ludo

  • May 21, 2010 at 3:30 am

    #1170674

    Guys

    Here is the solution:

    I have used the WITH clause to mimic the original table. The solution is quite obvious when you think about it as the XML will be based on Column names. We therefore have to PIVOT the table so the values in Col1 become columns themselves.

    WITH RawData AS

    (SELECT

    'ColA' Col1,

    1 Col2

    UNION

    SELECT

    'ColB',

    2

    UNION

    SELECT

    'ColC',

    3

    UNION

    SELECT

    'ColD',

    4

    )

    SELECT

    ColA,

    ColB,

    ColC,

    ColD

    FROM

    (SELECT

    Col1,

    Col2

    FROM

    RawData) AS pd

    PIVOT (

    SUM(Col2) FOR Col1 IN (ColA,

    ColB,

    ColC,

    ColD)

    ) AS t

    FOR XML PATH ('')

    Result:

    <ColA>1</ColA>

    <ColB>2</ColB>

    <ColC>3</ColC>

    <ColD>4</ColD>

    I hope this makes sense. 🙂

    Ludo

  • May 21, 2010 at 3:37 am

    #1170679

    Ooops sorry about the formating guys.

    Sorry to be so thick but I have tried different copy/paste and can't get the code to indent.

    Help?

    Ludo

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

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