sql - 在 SQL Server 的存储过程中解析 XML 并输出
问题描述
这是 XML 模式:
<details schemaVersion="1">
<dataWarning>
<Locations>
<Failed_to_Create LocationID="1234">Location<Location_Description>TEST</Location_Description><FacilityID>TEST</FacilityID><Active>Y</Active></Failed_to_Create>
</Locations>
</dataWarning>
</details>
如何解析 XML 以便能够在存储过程中显示它?
谢谢!
enter code here
解决方案
安德里亚,你需要更加自律。您有责任提供 DDL 和样本数据群。
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, Details XML);
INSERT INTO @tbl (Details)
VALUES
(N'<details schemaVersion="1">
<dataWarning>
<Locations>
<Failed_to_Create LocationID="1234">Location<Location_Description>TEST</Location_Description><FacilityID>TEST</FacilityID><Active>Y</Active></Failed_to_Create>
</Locations>
</dataWarning>
</details>')
, (N'<details schemaVersion="1">
<dataWarning>
<Locations>
<Failed_to_Create LocationID="5678">Location<Location_Description>Real Description</Location_Description><FacilityID>770</FacilityID><Active>N</Active></Failed_to_Create>
</Locations>
</dataWarning>
</details>');
-- DDL and sample data population, end
SELECT c.value('@LocationID', 'VARCHAR(10)') AS [locationID]
, c.value('(Location_Description/text())[1]', 'VARCHAR(100)') AS [Location_Description]
, c.value('(FacilityID/text())[1]', 'VARCHAR(100)') AS [FacilityID]
, c.value('(Active/text())[1]', 'VARCHAR(100)') AS [Active]
FROM @tbl AS tbl
CROSS APPLY tbl.Details.nodes('/details/dataWarning/Locations/Failed_to_Create') AS t(c);
输出
+------------+----------------------+------------+--------+
| locationID | Location_Description | FacilityID | Active |
+------------+----------------------+------------+--------+
| 1234 | TEST | TEST | Y |
| 5678 | Real Description | 770 | N |
+------------+----------------------+------------+--------+
推荐阅读
- python - Jupyter Notebook:在 /home 和/或隐藏文件之外编辑文件 - 解决方法
- reactjs - React Native Custom Hook call with Parameter Invalid Hook Call
- php - 如何重构这个功能?
- python - numpy shuffle 一部分子数组
- c# - 如何自己编写LINQ操作?
- c++ - CUDA 内核的奇怪行为
- python - 可以在没有通过字段的情况下创建 ManyToMany 吗?
- html - Visual Studio 2019 - cshtml colo 格式化
- css - 显示两个相邻的 div,然后在另一个下方显示两个不同的 SCSS 文件
- reactjs - 为全局搜索过滤器处理新的 Redux