locked
Insert XML data into a table RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks wBob, much appreciated.
    Tuesday, March 5, 2013 3:53 PM