Convert SQL table to XML

  • Hi,

    I want to convert table into following xml format. Sample two records is given below. Any help would be appreciated.

    <Row>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">XXXXXXXXXXXXX</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">64856</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">Test001</Data>

    </Cell>

    </ROW>

    <Row>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">XXXXXXXXXXXXX</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">64857</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">Test002</Data>

    </Cell>

    </ROW>

    Thanks,

    DH

  • You have given far too little information to go on, if you can post up the schema and sample data from your table it would help someone give you a specific answer.

    But have a read of this, it explains what you need to know about returning queries as XML:

    https://msdn.microsoft.com/en-us/library/ms178107.aspx">

    https://msdn.microsoft.com/en-us/library/ms178107.aspx

    MCITP SQL 2005, MCSA SQL 2012

  • Table schema is below. It contains three columns only

    Column AColumn BColumn C

    XXXXXXXX64856Test001

    XXXXXXXX64857Test002

  • devesh.hassani (10/5/2015)


    Table schema is below. It contains three columns only

    Column AColumn BColumn C

    XXXXXXXX64856Test001

    XXXXXXXX64857Test002

    Please see the link in my signature on posting code and data for the best help.

    E.g, your sample would look something like this

    CREATE TABLE #Temp

    (

    [Column A] VARCHAR(10),

    [Column B] VARCHAR(5),

    [Column C] VARCHAR(10)

    )

    INSERT INTO #Temp VALUES

    ('XXXXXXXXXX','64856','Test001'),

    ('XXXXXXXXXX','64857','Test002')

  • Thanks! Anthony.

    Correct my sample data will be as below.

    CREATE TABLE #Temp

    (

    [Column [A] VARCHAR(10),

    [Column VARCHAR(5),

    [Column [C] VARCHAR(10)

    )

    INSERT INTO #Temp VALUES

    ('XXXXXXXXXX','64856','Test001'),

    ('XXXXXXXXXX','64857','Test002')

  • XML is not a strength of mine but I think this is possible using the FOR XML EXPLICIT command

    https://technet.microsoft.com/en-us/library/aa226532(v=sql.80).aspx

    I don't have time to try and do this with your sample data right now, but I'll try and get back to this later today.

    MCITP SQL 2005, MCSA SQL 2012

  • Thanks! Taylor...Will look forward for your post. I tried using FOR XML Explicit, however, could not get the required XML structure...

  • Having done some testing this is definitely outside my level of knowledge with XML. Hopefully someone else can post up a solution.

    MCITP SQL 2005, MCSA SQL 2012

  • The code below might help:

    CREATE TABLE #Temp

    (

    [Column A] VARCHAR(10),

    [Column B] VARCHAR(5),

    [Column C] VARCHAR(10)

    )

    INSERT INTO #Temp VALUES

    ('XXXXXXXXXX','64856','Test001'),

    ('XXXXXXXXXX','64857','Test002')

    SELECT [Column A] AS ColumnA

    ,[Column B] AS ColumnB

    ,[Column C] AS ColumnC

    FROM #Temp

    FOR XML PATH ('Row')

  • itumelengd (10/5/2015)


    The code below might help:

    CREATE TABLE #Temp

    (

    [Column A] VARCHAR(10),

    [Column B] VARCHAR(5),

    [Column C] VARCHAR(10)

    )

    INSERT INTO #Temp VALUES

    ('XXXXXXXXXX','64856','Test001'),

    ('XXXXXXXXXX','64857','Test002')

    SELECT [Column A] AS ColumnA

    ,[Column B] AS ColumnB

    ,[Column C] AS ColumnC

    FROM #Temp

    FOR XML PATH ('Row')

    Unfortunatly the representation you get back doesn't match that requested:

    This is what they are looking for:

    <Row>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">XXXXXXXXXXXXX</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">64856</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">Test001</Data>

    </Cell>

    </ROW>

    This is what your code brings back:

    <Row>

    <ColumnA>XXXXXXXXXX</ColumnA>

    <ColumnB>64856</ColumnB>

    <ColumnC>Test001</ColumnC>

    </Row>

    <Row>

    <ColumnA>XXXXXXXXXX</ColumnA>

    <ColumnB>64857</ColumnB>

    <ColumnC>Test002</ColumnC>

    </Row>

    As you pointed out using FOR XML PATH seems to get closer to whats needed I too could not get the same format:

    select'StyleID="s22"' AS '@ss',

    ColumnA as Data1,

    ColumnB as Data2,

    ColumnC as Data3

    FROM #Temp t

    FOR XML PATH('Cell'), root('Row')

    Returns:

    <Row>

    <Cell ss="StyleID="s22"">

    <Data1>XXXXXXXXXX</Data1>

    <Data2>64856</Data2>

    <Data3>Test001</Data3>

    </Cell>

    <Cell ss="StyleID="s22"">

    <Data1>XXXXXXXXXX</Data1>

    <Data2>64857</Data2>

    <Data3>Test002</Data3>

    </Cell>

    </Row>

    The extra nesting should be possible using correlated subqueries in place of the ColumnX as DataX statements, but I still could not get the format matching although the nesting looked closer to what was requested.

    MCITP SQL 2005, MCSA SQL 2012

  • devesh.hassani (10/5/2015)


    Hi,

    I want to convert table into following xml format. Sample two records is given below. Any help would be appreciated.

    <Row>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">XXXXXXXXXXXXX</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">64856</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">Test001</Data>

    </Cell>

    </ROW>

    <Row>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">XXXXXXXXXXXXX</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">64857</Data>

    </Cell>

    <Cell ss:StyleID="s22">

    <Data ss:Type="String">Test002</Data>

    </Cell>

    </ROW>

    Thanks,

    DH

    It would appear that you're trying to export "spreadsheet data". Why not just do a simple export of TSV data instead of jumping through the XML and "format every cell" hoop? It would be a whole lot easier on "the pipe".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks! for the suggestion Jeff.

    Can you please explain how can we do the export from SQL as i am not sure on the process.

    Thanks,

    DH

  • How often does this need to run?

    Very ad-hoc you could look at the Import/Export Wizard

    Everyday/hour etc I would look at SSIS

    Also thanks to Jeff, hadn't seen spreadsheet XML like that will be one ingrained into the recesses of my mind

  • IMHO trying to use XML to format this is more trouble than it's worth.

    A bit of VARCHAR works nicely though.

    CREATE TABLE #Temp

    (

    [Column A] VARCHAR(10),

    [Column B] VARCHAR(5),

    [Column C] VARCHAR(10)

    )

    INSERT INTO #Temp VALUES

    ('XXXXXXXXXX','64856','Test001'),

    ('XXXXXXXXXX','64857','Test002');

    SELECT

    ' <Row>

    <Cell ss:StyleID="s22"><Data ss:Type="String">' + ISNULL( CAST( [Column A] AS VARCHAR), '') + '</Data></Cell>'+CHAR(13)

    + ' <Cell ss:StyleID="s22"><Data ss:Type="String">' + ISNULL( CAST( [Column B] AS VARCHAR), '') + '</Data></Cell>'+CHAR(13)

    + ' <Cell ss:StyleID="s22"><Data ss:Type="String">' + ISNULL( CAST( [Column C] AS VARCHAR), '') + '</Data></Cell>'+CHAR(13)

    + ' </Row>'

    FROM #Temp;

    However, I would recommend hitting Jeff up for some alternative ideas.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • devesh.hassani (10/5/2015)


    Thanks! for the suggestion Jeff.

    Can you please explain how can we do the export from SQL as i am not sure on the process.

    Thanks,

    DH

    I guess the first question to ask is how would you have done the export of XML to a file (presuming you wanted a file)? As soon as I know that, I can help using TSVs to export instead of XML.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Viewing 15 posts - 1 through 15 (of 20 total)

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