SQL Server 将XML字段拆分为多个分隔值- SQL

ttisahbt 于 2022-11-21 发布在其他

关注(0)|答案(2)|浏览(127)

I have some XML content in a single field; I want to split each xml field in multiple rows.
The XML is something like that:

<env>
    <id>id1<\id>
    <DailyProperties>
        <date>01/01/2022<\date>
        <value>1<\value>
    <\DailyProperties>
    <DailyProperties>
        <date>05/05/2022<\date>
        <value>2<\value>
    <\DailyProperties>
<\env>

I want to put everything in a table as:

ID      DATE            VALUE
id1     01/01/2022      1
id1     05/05/2022      2

For now I managed to parse the xml value, and I have found something online to get a string into multiple rows ( like this ), but my string should have some kind of delimiter. I did this:

SELECT
    ID,
    XMLDATA.X.query('/env/DailyProperties/date').value('.', 'varchar(100)') as r_date,
    XMLDATA.X.query('/env/DailyProperties/value').value('.', 'varchar(100)') as r_value
from tableX
outer apply xmlData.nodes('.') as XMLDATA(X)
WHERE ID = 'id1'

but I get all values without a delimiter, as such:
01/10/202202/10/202203/10/202204/10/202205/10/202206/10/202207/10/202208/10/202209/10/202210/10/2022
Or, as in my example:

ID      R_DATE                  R_VALUE
id01    01/01/202205/05/2022    12

I have found out that XQuery has a last() function that return the last value parsed; in my xml example it will return only 05/05/2022 , so it should exists something for address the adding of a delimiter. The number of rows could vary, as it could vary the number of days of which I have a value.

sql-server

来源：https://stackoverflow.com/questions/74464646/split-xml-field-into-multiple-delimited-values-sql

2条答案

按热度按时间

ee7vknir1#

请尝试以下解决方案。
我不得不修正 XML 以使其格式良好。

- 查询语句 * *

DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<env>
    <id>id1</id>
    <DailyProperties>
        <date>01/01/2022</date>
        <value>1</value>
    </DailyProperties>
    <DailyProperties>
        <date>05/05/2022</date>
        <value>2</value>
    </DailyProperties>
</env>');

SELECT p.value('(id/text())[1]','VARCHAR(20)') AS id
   , c.value('(date/text())[1]','VARCHAR(10)') AS [date]
   , c.value('(value/text())[1]','INT') AS [value]
FROM @tbl
CROSS APPLY xmldata.nodes('/env') AS t1(p)
   OUTER APPLY t1.p.nodes('DailyProperties') AS t2(c);

中的每一个

- 输出 * *

| 标识符|日期|价值观|
| - -| - -| - -|
| 识别码 1| 2022 年 1 月 1 日|一个|
| 识别码 1| 2022 年 5 月 5 日|2 个|

赞(0）回复(0）举报 2022-11-21

brc7rcf02#

伊扎克比我快了2分钟。尽管如此，我还是得到了以下信息：

--==== XML Data:
DECLARE @xml XML = 
'<env>
  <id>id1</id>
  <DailyProperties>
    <date>01/01/2022</date>
    <value>1</value>
  </DailyProperties>
  <DailyProperties>
    <date>05/05/2022</date>
    <value>2</value>
  </DailyProperties>
</env>';

--==== Solution:
SELECT 
  ID      = ff2.xx.value('(text())[1]','varchar(20)'),
  [Date]  = ff.xx.value('(date/text())[1]', 'date'),
  [Value] = ff.xx.value('(value/text())[1]', 'int')
FROM        (VALUES(@xml))                   AS f(X)
CROSS APPLY f.X.nodes('env/DailyProperties') AS ff(xx)
CROSS APPLY f.X.nodes('env/id')              AS ff2(xx);

退货：

ID                   Date       Value
-------------------- ---------- -----------
id1                  2022-01-01 1
id1                  2022-05-05 2

赞(0）回复(0）举报 2022-11-21

我来回答

SQL Server 将XML字段拆分为多个分隔值- SQL

2条答案

相关问题

热门标签

最新问答