我有一个包含XML列的表,它的名称是Bookmarks,选择查询的结果如下:
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>
此外,我还创建了一个包含Title
和Value
列的表。我想知道如何读取XML类型并将其内容插入到另一个表中?
您可以尝试使用XQuery来获取XML值。
查询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 |
如果您想从XML值插入到另一个表中,可以尝试以下操作。
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