問題描述

我知道如何在最簡單的情況下做到這一點，例如

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 數據中獲取逗號分隔值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！