sql-server - 使用 OpenXML 分解 XML
问题描述
我正在尝试粉碎以下 XML,但我无法使用 OPENXML 构造获得任何结果,但我的输出看起来不正确。关于如何重写这个有什么建议吗?
<?xml version="1.0" encoding="UTF-8"?> <results
xmlns="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns"
xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"
xs:schemaLocation="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns
https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/xsd">
<result>
<a>a1</a>
<b>2</b>
<c>a1332</c>
<d>text.</d>
<e>Risk 2</e>
<f> </f>
<g>a123</g>
<h>1223324aaa</h>
<i>l1245</i>
<j>Complete</j>
<k>Not yet reported</k> </result>
请注意以下是我正在使用的代码片段
DECLARE @xml XML;
DECLARE @idoc INT;
SELECT @xml = CONVERT(XML, cast(results AS VARCHAR(MAX)), 2) FROM stg.requirements;
EXEC sys.sp_xml_preparedocument @idoc OUTPUT
,@xml
,'<results xmlns="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" xs:schemaLocation="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/xsd"/>';
SELECT *
FROM
OPENXML(@idoc, '/*', 1)
WITH ()
EXEC sys.sp_xml_removedocument @idoc;
--SELECT * FROM #temp
DROP TABLE IF EXISTS #temp
解决方案
有几点需要指出。
(1) 你的 XML 格式不正确,所以我不得不修复它。
(2) 从 SQL Server 2005 开始,使用 XQuery 语言,基于 w3c 标准,处理 XML 数据类型。Microsoft 的专有OPENXML
及其伙伴sp_xml_preparedocument
,sp_xml_removedocument
只是为了向后兼容过时的 SQL Server 2000。这就是为什么使用.nodes()
(3) 始终应考虑命名空间。
(4) 方法中正确的 SQL Server 数据类型.value()
。
SQL
DECLARE @xml XML = '<?xml version="1.0" encoding="UTF-8"?>
<results xmlns="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns"
xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"
xs:schemaLocation="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/xsd">
<result>
<a>a1</a>
<b>2</b>
<c>2020-02-15</c>
<d>text.</d>
</result>
<result>
<a>a7</a>
<b>25</b>
<c>2020-01-25</c>
<d>Another text</d>
</result>
</results>';
;WITH xmlnamespaces (DEFAULT 'https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns')
SELECT c.value('(a/text())[1]', 'VARCHAR(10)') AS a
, c.value('(b/text())[1]', 'INT') AS b
, c.value('(c/text())[1]', 'DATE') AS c
, c.value('(d/text())[1]', 'VARCHAR(30)') AS d
FROM @xml.nodes('/results/result') AS t(c);
输出
+----+----+------------+--------------+
| a | b | c | d |
+----+----+------------+--------------+
| a1 | 2 | 2020-02-15 | text. |
| a7 | 25 | 2020-01-25 | Another text |
+----+----+------------+--------------+
推荐阅读
- regex - 从字符串末尾开始查找 DATE 匹配项
- mysql - 导入命令行不适用于 SSH Putty
- list - 将嵌套列表转换为字典
- firefox - 尝试使用 Facebook 图形 API 托管静态网站的 Github 页面时,CORS 请求未成功
- terraform - Terraform 的 NSX-T 提供程序未找到第 0 层路由器
- c - MAP_HUGETLB 未定义
- c++ - 调用 QML 处理程序,但使用“未定义”c++ 信号参数
- validation - ASP.NET CORE 3.1 中数据注释和错误验证消息的本地化
- python - 从另一列中减去日期时间。出现错误:-: 'str' 和 'str' 的操作数类型不受支持
- javascript - 从 eBay 中的 Find Items Advanced API 返回项目属性