从 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 数据中获取逗号分隔值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

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

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
SSIS: Model design issue causing duplications - can two fact tables be connected?(SSIS:模型设计问题导致重复-两个事实表可以连接吗?)
SQL Server Graph Database - shortest path using multiple edge types(SQL Server图形数据库-使用多种边类型的最短路径)
Invalid column name when using EF Core filtered includes(使用EF核心过滤包括时无效的列名)
How should make faster SQL Server filtering procedure with many parameters(如何让多参数的SQL Server过滤程序更快)
How can I generate an entity–relationship (ER) diagram of a database using Microsoft SQL Server Management Studio?(如何使用Microsoft SQL Server Management Studio生成数据库的实体关系(ER)图?)