从 xml 数据中获取逗号分隔值

get comma separated values from xml data(从 xml 数据中获取逗号分隔值)
本文介绍了从 xml 数据中获取逗号分隔值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我知道如何在最简单的情况下做到这一点,例如

I know how to do it in the simplest scenario, e.g.

DECLARE @commaSeparatedValues NVARCHAR(MAX)
DECLARE @xml XML = N'<id>1</id>
<name>test</name>
<istest>1</istest>'

;WITH nodes AS 
(
    SELECT Tbl.Col.value('.', 'nvarchar(max)') as Value
    FROM @xml.nodes('*/text()') Tbl(Col)
),
prepareStrings
AS
(
    SELECT IIF(ISNUMERIC(n.value) = 1, n.Value, '''' + n.Value + '''') AS Value
    FROM nodes n
)
SELECT @commaSeparatedValues = CASE WHEN @commaSeparatedValues IS NULL THEN s.Value ELSE @commaSeparatedValues + ',' + s.value END
FROM prepareStrings s

SELECT @commaSeparatedValues as csv

这完美地工作.当我想以这种方式解析以下 xml 数据时出现问题.我在编写正确的查询时遇到问题.

This works perfectly. Problem arises when I want to parse this way the following xml data. I have problems with writing the proper query.

DECLARE @xml XML = N'
<e>
  <id>1</id>
  <name>test</name>
  <istest>1</istest>
</e>
<e>
  <id>2</id>
  <name>test2</name>
  <istest>0</istest>
</e>
'

我可以通过使用逐行获取元素

I can get the elements row by row by using

select Tbl.col.query('.') as [xml]
from @xml.nodes('e') Tbl(col)

我不知道如何向前推进.不知道如何使用这个查询,现在查询 [xml] 列.

What I don't know is how to move forward with this. Don't know how to use this query and now querying the [xml] column.

推荐答案

请尝试下面的 SQL 查询

Please try the below SQL query

DECLARE @commaSeparatedValues NVARCHAR(MAX)
DECLARE @xml XML = N'
<e>
  <id>1</id>
  <name>test1</name>
  <istest>1</istest>
</e>
<e>
  <id>2</id>
  <name>test2</name>
  <istest>2</istest>
</e>
'

;with cte as (
    select 
        rownr = ROW_NUMBER() over (order by @commaSeparatedValues),
        Tbl.col.query('.') as [xml]
    from @xml.nodes('e') Tbl(col)
), cols as (
    select
        rownr,
        Tbl.Col.value('.', 'nvarchar(max)') as Value
    from cte
    cross apply cte.xml.nodes('//text()') Tbl(Col)
)
select distinct 
     STUFF((
       SELECT ',' + IIF(ISNUMERIC(value) = 1, Value, '''' + Value + '''')
       FROM cols SSF WHERE SSF.rownr = S.rownr
       FOR XML PATH(''),TYPE
       ).value('.','VARCHAR(MAX)'
     ), 1, 1, '')
    from cols S

我使用 SQL row_number()作用对记录进行编号,区分列值时区分列值(第二个CTE使用Partition By子句对行数据中的列进行排序)

I use SQL row_number() function to number records and distinguish column values when they are separated into values (the second CTE uses Partition By clause to sort columns among row data)

然后我使用 SQL 字符串连接 使用 XML PATH() 的方法

Then I concatenate string values into comma separated string using SQL string concatenation method with using XML PATH()

希望能帮到你

这篇关于从 xml 数据中获取逗号分隔值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Convert varchar to float IF ISNUMERIC(将 varchar 转换为 float IF ISNUMERIC)
multi part identifier could not be bound sql(无法绑定多部分标识符 sql)
Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?(在存储过程结束时显式删除本地临时表有什么好处?)
Check if a column contains text using SQL(使用 SQL 检查列是否包含文本)
Select value if condition in SQL Server(SQL Server 中的条件选择值)
Control flow in T-SQL SP using IF..ELSE IF - are there other ways?(使用 IF..ELSE IF 在 T-SQL SP 中控制流 - 还有其他方法吗?)