我需要将以下文本转换为 XML

{"name":"daniel & sophia","age":20,"year":2009,"weight":15.1,"points":3,"alias":"dani,da"}{"name":"charls & lina","age":22,"year":2007,"weight":19.0"points":3,"alias":"carlos,lini"}

到

<participants>
    <participant>
        <name>daniel & sophia</name>
        <age>20</age>
        <year>2009</year>
        <weight>15.1</weight>
        <points>3</points>
        <alias>dani,da</alias>
    </participant>
    <participant>
        <name>charls & lina</name>
        <age>22</age>
        <year>2007</year>
        <weight>19.0</weight>
        <points>3</points>
        <alias>carlos,lini</alias>
    </participant>
<participants>

我尝试将数据插入临时表，然后替换“{}”字符。然后我尝试使用 XML 函数进行转换，但我真的不知道如何复制每个项目的名称。

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
            DROP TABLE #tmp

CREATE TABLE #tmp
(
    Id INT IDENTITY,
    Campo VARCHAR(MAX)
)

INSERT INTO #tmp
(
    Campo
)
VALUES
(
'{"name":"daniel & sophia","age":20,"year":2009,"weight":15.1,"points":3,"alias":"dani,da"}{"name":"charls & lina","age":22,"year":2007,"weight":19.0"points":3,"alias":"carlos,lini"}'
)

SELECT 
        CONVERT
        (
            XML, '<participants>' + 
            REPLACE
            (
                REPLACE
                (
                    REPLACE
                    (
                        REPLACE(REPLACE((SELECT Campo AS [*] FOR XML PATH('')), '{', '<participant>'),'}','</participant>') + '</participants>'
                        ,'<participant>"'
                        ,'<participant><dato>'
                    )
                    , '","'
                    , '</dato><dato>'
                )
                , '</participant>'
                , '</dato></participant>'
            )
        ) 

            AS xmlname
    FROM #tmp

这是我得到的，但这是错误的:

<participants>
  <participant>
    <dato>name":"daniel &amp; sophia</dato>
    <dato>age":20,"year":2009,"weigth":15.1,"points":3,"alias":"dani,da"</dato>
  </participant>
  <participant>
    <dato>name":"charls &amp; lina</dato>
    <dato>age":22,"year":2007,"weigth":19.0,"points":3,"alias":"carlos,lini"</dato>
  </participant>
</participants>

NOTE: The amount of The number of nodes within the Participant node is unknown, it can be more than 100 and I would really like it to be a dynamic query. (Without using EXEC "sql code")

最佳答案

John Cappelletti 的回答很好，只要您提前知道列名即可。以下方法将在您必须动态处理此类结构的情况下为您提供帮助。

在字符串级别创建 XML 很丑陋(就像我在最后一个 SELECT 中所做的那样 SELECT '<' + innerNvp.Name + '>' + ，但动态处理列名是可行的。否则你要么必须知道提前所有列，否则您需要使用 EXEC 走动态 SQL 的路径。要记住一件事:结构中的名称(如 "name" 必须有效XML 标记名称。

一个一般性提示:这里的所有方法都试图在字符串级别削减您的部分。如果有 }，这可能会中断或 ,"或 :在意想不到的地方...

DECLARE @str NVARCHAR(MAX)='{"name":"daniel & sophia","age":20,"year":2009,"weigth":15.1,"points":3,"alias":"dani,da"}{"name":"charls & lina","age":22,"year":2009,"weigth":15.1,"points":3,"alias":"carlos,lini"}';

WITH SplittedAtClosingCurly AS
(
    SELECT CAST('<x>' + REPLACE((SELECT @str AS [*] FOR XML PATH('')),'}','</x><x>') + '</x>' AS XML) AS TheRows
)
,SplittedAtCommaQuote AS
(
    SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNr
           ,CAST('<x>' + REPLACE((SELECT Rw.value(N'text()[1]','nvarchar(max)') AS [*] FOR XML PATH('')),',"','</x><x>') + '</x>' AS XML)  AS TheRow
    FROM SplittedAtClosingCurly
    CROSS APPLY TheRows.nodes(N'/x[text()]') AS A(Rw)
)
,SplittedAtDoubleDot AS
(
    SELECT  RowNr
           ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS TplNr
           ,CAST('<x>' + REPLACE((SELECT Tpl.value(N'text()[1]','nvarchar(max)') AS [*] FOR XML PATH('')),':','</x><x>') + '</x>' AS XML)  AS TheTupel
    FROM SplittedAtCommaQuote
    CROSS APPLY TheRow.nodes(N'/x[text()]') AS A(Tpl) 
)
,DerivedTable_NameValuePairs AS
(
    SELECT RowNr
          ,TplNr
          ,REPLACE(REPLACE(TheTupel.value(N'/x[1]/text()[1]','nvarchar(max)'),'{',''),'"','') AS Name
          ,REPLACE(REPLACE(TheTupel.value(N'/x[2]/text()[1]','nvarchar(max)'),'{',''),'"','') AS Value
    FROM SplittedAtDoubleDot
)

SELECT CAST(
           (
            SELECT '<' + innerNvp.Name + '>' + (SELECT innerNvp.Value AS [*] FOR XML PATH('')) + '</' + innerNvp.Name + '>'
            FROM DerivedTable_NameValuePairs AS innerNvp
            WHERE innerNvp.RowNr=nvp.RowNr
            ORDER BY TplNr
            FOR XML PATH(''),TYPE
           ).value(N'text()[1]','nvarchar(max)') AS XML)
FROM DerivedTable_NameValuePairs AS nvp
GROUP BY RowNr
FOR XML PATH('participant'),ROOT('participants')

关于sql - 如何在 SQL Server 2008 中将格式为 "property:value,property2:value"的字符串转换为 xml，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/43881366/