如何使用SQL Server获取XML中的父节点列表? [英] How can I get list of parent node in XML with SQL server?
本文介绍了如何使用SQL Server获取XML中的父节点列表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
假设我有xml数据类型的表。将以下值视为xml中的数据。
< 图书 >
< < span class =code-leadattribute> Book ID = BK1 >
< 页面 >
< Page ID = 主页 / >
&l t; Page ID = 索引 / >
< 页 ID = 简介 / >
< / Pages >
< / Book >
< Book ID = BK2 >
< 页面 >
< Page ID = 主页 / >
< Page ID = 简介 / >
< / Pages >
< < span class =code-leadattribute> / Book >
< Book ID = BK3 >
< 页面 >
< Page ID = 主页 / >
< 页 ID = 索引 / >
< < span class =code-leadattribute> Page ID = 简介 / >
< /页 >
< / Book >
< Book ID = BK4 >
< 页面 >
< 页 ID = 主页 / >
< < span class =code-leadattribute> Page ID = 简介 / >
< / Pages >
< / Book >
< < span class =code-leadattribute> / Books >
我想要页面ID =索引缺失的书籍ID列表? (例如,在书ID = BK2和ID = BK4,页面ID =索引缺失?
输出:
BookId
BK2
BK4
我尝试过:
SELECT Data.Col.value(' (@ ID)[1]',' Varchar(100)') AS 图书
FROM @ x .nodes(' / Books / Book / Pages / Page' ) AS 数据(Col)
WHERE @ x .exist(' (/ Books / Book / Pages / Page [@ID = sql:variable(@ BookId)])')= 1
解方案
你并不需要指定在存在
测试的完整路径;你只想测试当前的< Book>
节点,而不是整个文档:
< span class =code-keyword> SELECT data.col.value(' @ ID',' varchar(100)')
FROM @ x .nodes(' / Books / Book')作为数据(col)
WHERE 数据.col.exist(' Pages / Page [@ ID =Index]')= 0 跨度>;
Hi All,
Assume I have table with xml datatype. Consider below value as data in xml.
<Books>
<Book ID="BK1">
<Pages>
<Page ID="Home" />
<Page ID="Index" />
<Page ID="Introduction" />
</Pages>
</Book>
<Book ID="BK2">
<Pages>
<Page ID="Home" />
<Page ID="Introduction" />
</Pages>
</Book>
<Book ID="BK3">
<Pages>
<Page ID="Home" />
<Page ID="Index" />
<Page ID="Introduction" />
</Pages>
</Book>
<Book ID="BK4">
<Pages>
<Page ID="Home" />
<Page ID="Introduction" />
</Pages>
</Book>
</Books>
I want list of book ID for which Page ID=Index is missing? (For ex, in Book ID=BK2 and ID=BK4, Page ID=Index is missing?
Output:
BookId
BK2
BK4
What I have tried:
SELECT Data.Col.value('(@ID)[1]','Varchar(100)') AS Books
FROM @x.nodes('/Books/Book/Pages/Page') AS Data(Col)
WHERE @x.exist('(/Books/Book/Pages/Page[@ID = sql:variable("@BookId")])') = 1
解决方案
You don't need to specify the full path in theexist
test; you just want to test against the current<Book>
node, rather than the whole document:
SELECT data.col.value('@ID', 'varchar(100)') FROM @x.nodes('/Books/Book') As data(col) WHERE data.col.exist('Pages/Page[@ID="Index"]') = 0;
这篇关于如何使用SQL Server获取XML中的父节点列表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文