sqlsql-serverxmlxquery-sql

How to insert XML column of a table in to another table incuding the same fields


I have a table including XML column which it's Name is Bookmarks and result of select query would be Like this:

SELECT [Bookmarks] FROM [prs_Decrees] 

<Bookmarks>
  <Bookmark BookmarkGuid="f892e0ef-a498-4ece-9933-ab0154cd6c90" Title="AcceptableWorkinDaysMinusAbsenceDays" Value="-2" />
  <Bookmark BookmarkGuid="fe149c4d-1ae9-43c5-9403-11c06b8be567" Title="AcceptableWorkinDaysMinusAbsenceMonths" Value="-3" />
  <Bookmark BookmarkGuid="53669ea1-bc5b-4177-af4f-9642d1419549" Title="AcceptableWorkinDaysMinusAbsenceYears" Value="-4" />
  <Bookmark BookmarkGuid="e85da9b9-31a4-4ee4-a0b1-598678a631a4" Title="PersonnelExtra" Value="Teacher"/>
</Bookmarks>

beside, I've created a table including Title and Value columns. I want to know how to read XML type and insert it's content to another table?


Solution

  • You can try to use XQuery to get XML values.

    Query 1:

    SELECT x.v.value('(@Title)[1]', 'varchar(200)') Title,
            x.v.value('(@Value)[1]', 'varchar(200)') Value  
    FROM [prs_Decrees] t  
    CROSS APPLY t.Bookmarks.nodes('/Bookmarks/Bookmark') as x(v)
    

    Results:

    |                                  Title |   Value |
    |----------------------------------------|---------|
    |   AcceptableWorkinDaysMinusAbsenceDays |      -2 |
    | AcceptableWorkinDaysMinusAbsenceMonths |      -3 |
    |  AcceptableWorkinDaysMinusAbsenceYears |      -4 |
    |                         PersonnelExtra | Teacher |
    

    if you want to insert to another table from XML values, you can try below.

    INSERT INTO anotherTable (Title,Value)
    SELECT x.v.value('(@Title)[1]', 'varchar(200)') Title,
            x.v.value('(@Value)[1]', 'varchar(200)') Value  
    FROM [prs_Decrees] t  
    CROSS APPLY t.Bookmarks.nodes('/Bookmarks/Bookmark') as x(v)
    

    sqlfiddle