Split XML field into multiple delimited values - SQL

Question

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.

**Please** Do not store dates in a text columns. Use a DATE data type and convert the date to `YYYY/MM/DD` so it is usable as such later in your developement — RiggsFolly, Nov 16 '22 at 17:21
this does not appear to be mysql; please tag with your actual database — ysth, Nov 16 '22 at 17:23
I can only interrogate the database, I will use a select in a database and save everything on another database; when I will achieve a single value, I will cast accordingly. — Pleasant94, Nov 16 '22 at 17:24

score 1 · Accepted Answer · answered Nov 16 '22 at 18:22

Please try the following solution.

I had to fix your XML to make it well-formed.

SQL

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

Output

id	date	value
id1	01/01/2022	1
id1	05/05/2022	2

Thank you. For better understandement, what you did was: specify the parsed value as text with `text()` function, extracting the first value with `[1]` because `value()` requires a singleton so you force the return of 1 (and only) value, explicit definition of the array type with `[date]` and `[value]`, and most importantly, parsing the xml then re-parsing the `DailyProperties` node so to access them orderly with `c.value()`. Am I right? — Pleasant94, Nov 17 '22 at 08:29

score 1 · Answer 2 · answered Nov 16 '22 at 18:26

Yitzhak beat me to it by 2 min. Nonetheless, here's what I have:

--==== 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);

Returns:

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

Split XML field into multiple delimited values - SQL

2 Answers2