您的位置: 首页  >  技术问答  >  如何从SQL中的xml节点提取数据

如何从SQL中的xml节点提取数据

分类: 技术问答 • 2022-07-04 15:46:44
问题描述：

以下是我的xml，它记录在数据库表中。如何从SQL中的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

答

我明白这一点，如下所示：

  • 这个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>')

您正在寻找--The ID可以

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

--This命令使用.query()首先要得到正确的节点上手，比.value得到的文本内容被定义为参数

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

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

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

它是确定使用CROSS APPLY .nodes()（像在其他的答案），但是 - 如果你一）不要指望多行，或b）想从一个位置读出不同的值，它是一种的开销...

+0

谢谢@Shnugo。您的解决方案有助于解决问题 – Dreammind

答

这样的事情，如果我得到你的权利（“仅”是不明确的），这里是一个例子：

DECLARE @xml 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>' 

SELECT n.c.value('@id', 'nvarchar(max)') FROM 
@xml.nodes('/root/pagelocation/webpart') AS n(c)

输出：

editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127 
editabletexttitle;a36d4858-5d61-49b6-a860-221ad0b72310 
editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d 
editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127

答

本u使用XPath表达式来查找具有特定ID的节点。这使用SQL Server中的nodes()函数CROSS APPLY将其应用于表中的XML，并使用value()函数进行选择。

如果您不清楚这是如何工作的：研究XPath表达式，SQL Server中的XPath/XQuery功能和CROSS APPLY。

DECLARE @t TABLE(x XML); 
INSERT INTO @t(x)VALUES('<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>'); 

SELECT 
    n.v.value('.','NVARCHAR(256)') 
FROM 
    @t AS t 
    CROSS APPLY t.x.nodes('//webpart[@id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d"]') AS n(v);

相关推荐