locked
Insert XML data into a table RRS feed

  • Question

  • Hello All

    Some time back i was given some great help to create the following CTE which created XML data from a temporary table:

    ;WITH xmlparse AS (SELECT portfoliostudyid, Cast('<M>' + Replace( alltopic, ',', '</M><M>') + '</M>' AS XML) AS alltopic
             FROM   ##ts1)
    --INSERT INTO ##ts2
    --   portfoliostudyid, S.a.value('.', 'VARCHAR(100)') AS alltopic
    --FROM  xmlparse  CROSS APPLY alltopic.nodes('/M') S(a); 
    SELECT portfoliostudyid, S.a.value('.', 'VARCHAR(100)') AS alltopic
    FROM  xmlparse  CROSS APPLY alltopic.nodes('/M') S(a); 

    However i really need to write this result set back to a query though. I know i'm doing it wrong, that you can't just write XML data back to a 'normal' table, this is what i've been trying to do:

    ;WITH xmlparse AS (SELECT portfoliostudyid, Cast('<M>' + Replace( alltopic, ',', '</M><M>') + '</M>' AS XML) AS alltopic
             FROM   ##ts1)
    INSERT INTO ##ts2
       (portfoliostudyid, S.a.value('.', 'VARCHAR(100)') AS alltopic)
    FROM  xmlparse  CROSS APPLY alltopic.nodes('/M') S(a); 
    This doesnt work though.  Any advice at all?

    best wishes


    Dave

    Tuesday, March 5, 2013 10:16 AM

Answers

  • You can insert XML into a table, using the XML datatype.

    I've corrected your lower query but it's not 100% clear what you are trying to do:

    ;WITH xmlparse AS (SELECT portfoliostudyid, Cast('<M>' + Replace( alltopic, ',', '</M><M>') + '</M>' AS XML) AS alltopic FROM   ##ts1)
    INSERT INTO ##ts2
    SELECT portfoliostudyid, S.a.value('.', 'VARCHAR(100)') AS alltopic
    FROM  xmlparse  CROSS APPLY alltopic.nodes('/M') S(a); 

    • Proposed as answer by Kalman Toth Tuesday, March 5, 2013 12:06 PM
    • Marked as answer by Dave Papworth Tuesday, March 5, 2013 3:52 PM
    Tuesday, March 5, 2013 11:28 AM

All replies

  • You can insert XML into a table, using the XML datatype.

    I've corrected your lower query but it's not 100% clear what you are trying to do:

    ;WITH xmlparse AS (SELECT portfoliostudyid, Cast('<M>' + Replace( alltopic, ',', '</M><M>') + '</M>' AS XML) AS alltopic FROM   ##ts1)
    INSERT INTO ##ts2
    SELECT portfoliostudyid, S.a.value('.', 'VARCHAR(100)') AS alltopic
    FROM  xmlparse  CROSS APPLY alltopic.nodes('/M') S(a); 

    • Proposed as answer by Kalman Toth Tuesday, March 5, 2013 12:06 PM
    • Marked as answer by Dave Papworth Tuesday, March 5, 2013 3:52 PM
    Tuesday, March 5, 2013 11:28 AM
  • Thanks wBob, much appreciated.
    Tuesday, March 5, 2013 3:53 PM