作者YiMMiY (YiMMiY)
看板Database
标题[SQL ] 请教xml栏位节点筛选的问题
时间Mon Sep 17 22:19:20 2018
MS SQL - 2012
各位好
现在有一张蛮单纯的资料表
Table1
{
Pkey nvachar(max)
XmlData xml
}
假设资料内容
Pkey XmlData
---------------------------------------
One <root attr1="One" attr2="XXX">
<AAA> aaaOne </AAA>
<BBB> bbbOne </BBB>
<CCC> ccc1One </CCC>
<CCC att="2"> ccc2One </CCC>
<DDD> dddOne </DDD>
<root>
Two <root attr1="Two" attr2="XXX">
<AAA> aaaTwo </AAA>
<BBB> bbbTwo </BBB>
<CCC> ccc1Two </CCC>
<CCC att="2"> ccc2Two </CCC>
<DDD> dddTwo </DDD>
<root>
...後面还有很多笔
现在我想捞BBB CCC，加上查询条件(EX：Pkey='One' OR Pkey='Two')
SELECT Pkey, XmlData.query('/root/BBB , /root/CCC ') AS XmlData FROM Table1
WHERE Pkey='One' OR Pkey='Two'
会得到
Pkey XmlData
---------------------------------------
One <BBB> bbbOne </BBB>
<CCC> ccc1One </CCC>
<CCC att="2"> ccc2One </CCC>
Two <BBB> bbbTwo </BBB>
<CCC> ccc1Two </CCC>
<CCC att="2"> ccc2Two </CCC>
但我希望是能得到
Pkey XmlData
---------------------------------------
One <root attr1="One" attr2="XXX">
<BBB> bbbOne </BBB>
<CCC> ccc1One </CCC>
<CCC att="2"> ccc2One </CCC>
<root>
Two <root attr1="Two" attr2="XXX">
<BBB> bbbTwo </BBB>
<CCC> ccc1Two </CCC>
<CCC att="2"> ccc2Two </CCC>
<root>
也就是包完整的XML路径
请问该如何查询?
谢谢
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 223.136.186.219
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1537193963.A.22E.html