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

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?

CodePudding user response：

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