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

2021-09-10 00:00:00 xml tsql xquery sql-server

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

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()

希望能帮到你

相关文章