使用 SQL 查询进行 XML 解析 [英] XML Parsing with SQL query

问题描述

我正在尝试在 sql server 中解析以下 xml 以获取所有 3 个 Id

I am trying to parse the below xml in sql server to get all the 3 Ids

<Configuration>
  <ID>1000</ID>
  <ID>1001</ID>
  <ID>1002</ID>
</Configuration>

使用查询

SELECT  CONFIGURATION.value('/', 'varchar(200)') as SID FROM SCHEDULE 

我得到的结果是 100010011002但我希望以列或 CSV 格式显示结果.

am getting the results as 100010011002 but i would like to have the results in a column or in a CSV format.

任何帮助都会得到帮助.

any help would be appriciated.

推荐答案

使用 MS SQL Server，这会给你行.

Using MS SQL Server, this will give you rows.

declare @xml as xml
set @xml = 
'<Configuration>
  <ID>1000</ID>
  <ID>1001</ID>
  <ID>1002</ID>
</Configuration>'

select
    x.i.value('.', 'int') as ID
from @xml.nodes('/Configuration/ID') as x(i)

<小时>

如果您只需要一个像本例中那样的值，在 text() 节点上切碎要快得多(三倍).

---

If you only need one value like in this example it is a lot faster (three times) to shred on the text() node.

select
    x.i.value('.', 'int') as ID
from @xml.nodes('/Configuration/ID/text()') as x(i)

这篇关于使用 SQL 查询进行 XML 解析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！