sql - SQL Server 中的 XML 比较(字符串)
问题描述
我在两个单独的表中有两个 XML 字符串。
**ROOT**
**Node ID=** 1
**name** vignesh **/name**
**street** 1211 **/street**
**/Node**
**Node ID=** 2
**name** ram **/name**
**street** 333 **/street**
**/Node**
**/ROOT**
**ROOT**
**Node ID=** 1
**name** newbie **/name**
**street** 121223 **/street**
**/Node**
**Node ID=** 2
**name** pro **/name**
**street** 445**/street**
**/Node**
**/ROOT**
请帮助我在SQL Server中找到这两个 XML 之间的比较,并将结果作为 ID1 和 ID2(节点)的旧值和新值给出。
解决方案
我相信这会对你有所帮助。我通过考虑您的 XML 数据来构建适当的 XML 结构。
DECLARE @XMLString1 XML =
'<ROOT>
<Nodes>
<NodeID>1</NodeID>
<name>vignesh </name>
<street>1211</street>
</Nodes>
<Nodes>
<NodeID>2</NodeID>
<name>ram</name>
<street>333</street>
</Nodes>
</ROOT>' ,
@XMLString2 XML =
'<ROOT>
<Nodes>
<NodeID>1</NodeID>
<name>newbie </name>
<street>121223</street>
</Nodes>
<Nodes>
<NodeID>2</NodeID>
<name>pro</name>
<street>445</street>
</Nodes>
</ROOT>'
SELECT @XMLString1,@XMLString2
;WITH XMLDATA1 AS
(
SELECT T.X.value('NodeID[1]','varchar(50)') AS NODEID,
T.X.value('name[1]','varchar(50)') AS NAME,
T.X.value('street[1]','varchar(50)') AS STREET
FROM @XMLString1.nodes('/ROOT/Nodes')T(X)
)
,XMLDATA2 AS
(
SELECT T.X.value('NodeID[1]','varchar(50)') AS NODEID,
T.X.value('name[1]','varchar(50)') AS NAME,
T.X.value('street[1]','varchar(50)') AS STREET
FROM @XMLString2.nodes('/ROOT/Nodes')T(X)
)
SELECT X1.NODEID,X1.NAME AS OLD_NAME,X2.NAME AS NEW_NAME , X1.STREET AS OLD_STREET , X2.STREET AS NEW_STREET
FROM XMLDATA1 X1
INNER JOIN XMLDATA2 X2 ON X1.NODEID=X2.NODEID
推荐阅读
- python - How to move files from a directory to another?
- android - Im having trouble populating ListTile in ListView.builder from database
- servicestack - AutoQuery/OrmLite incorrect total value when using joins
- java - 如何从 Json 字符串动态创建按钮
- c++ - Is there any way to access QByteArray member which is member of a union
- python-3.x - 从scrapy爬取更新sqlite DB中的数据的问题
- sitecore - 挂钩到项目选择操作
- hibernate - Flyway & MariaDB:SQLException 外键约束的格式不正确
- variables - 如何将命令输出保存到变量
- algorithm - 为什么此代码通过 Prim 的算法生成错误的最小生成树?