sql - 如何在SQL中从xml节点提取数据

Question

下面是我的xml，它记录在数据库表中。

<root>
  <pagelocation>
    <nodeid>3178</nodeid>
      <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
            Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
        </webpart>
    </pagelocation>
    <pagelocation>
        <NodeId>3180</NodeId>
            <webpart id="editabletexttitle;a36d4858-5d61-49b6-a860-221ad0b72310">
                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
            <webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d">
                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
            <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
                        Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
      </pagelocation>
</root>

我只需要获取属于的信息
<webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d">如何在SQL中针对此类内容编写查询

Answer 1

我了解如下：


此XML（以及许多其他XML）位于表的列中，行很多
这些XML中的每一个都有或多或少的<webpart>节点
在一个XML中，@id是唯一的
您想阅读此给定的webpart元素内的文本


以下代码将在模拟三个不同情况的声明表中插入三行。

DECLARE @tbl TABLE(ID INT IDENTITY,Descritpion VARCHAR(100),XmlColumn XML);
INSERT INTO @tbl VALUES
('Contains the Id'
,N'<root>
    <pagelocation>
        <NodeId>3180</NodeId>
            <webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d">
                    Some Content 
            </webpart>
      </pagelocation>
</root>')
,('Does not contain the Id'
,N'<root>
  <pagelocation>
    <nodeid>3178</nodeid>
      <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
            Other Content 
        </webpart>
    </pagelocation>
</root>')
,('Multiple IDs, one of them fitting'
,N'<root>
  <pagelocation>
    <nodeid>3178</nodeid>
      <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
            id is not correct
        </webpart>
    </pagelocation>
    <pagelocation>
        <NodeId>3180</NodeId>
            <webpart id="editabletexttitle;a36d4858-5d61-49b6-a860-221ad0b72310">
                    Same here 
            </webpart>
            <webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d">
                    Yeah! that is is 
            </webpart>
            <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
                    One more 
            </webpart>
      </pagelocation>
</root>')

-您要搜索的ID可以定义为参数

DECLARE @SearchFor NVARCHAR(100)=N'editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d';

-该命令首先使用 .query()来获取正确的节点，然后使用 .value来获取文本内容

SELECT *
      ,XmlColumn.query('//webpart[@id=sql:variable("@SearchFor")]').value('.','nvarchar(max)') AS Content
FROM @tbl;

-此命令直接使用 XQuery中的 .value()（更快，在第一次出现时停止）

SELECT *
      ,XmlColumn.value('(//webpart[@id=sql:variable("@SearchFor")])[1]','nvarchar(max)') AS Content
FROM @tbl

可以使用 CROSS APPLY .nodes()（就像在其他答案中一样），但是-如果您a）不要期望多于一行，或者b）想从一个位置读取不同的值，那是一种开销...