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?
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)
| 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)