Измельчение данных столбца XML в строки в SQL

У меня есть значение xml

<ITEMS>
<ITEM><ID>1</ID><NAME>John</NAME></ITEM>
<ITEM><ID>5</ID><NAME>James</NAME></ITEM>
</ITEMS>

Я могу разбить приведенный выше xml на таблицы столбцов ID и Name, используя приведенный ниже запрос.

Declare @X xml
select x.r.value('(ID)[1]','int') as [ID],
       x.r.value('(DATA)[1]','VARCHAR(100)') AS [DATA]
FROM @X.nodes ('/ITEMS/ITEM') AS x(r)

Но как я смогу это сделать, когда указанный выше xml присутствует в строке.

S.No   COMPANY        DATA
 1      ABC      </ITEMS><ITEM><ID>1</ID><NAME>John</Name>....

Мне нужно заполнить, как показано ниже

S.No   COMPANY    ID     NAME
 1      ABC        1    John
 2      ABC        5    James

Примечание. Столбец данных в таблице имеет тип данных varchar, а не тип данных xml.


xml sql-server xquery-sql sqlxml shred
person Gladstone Jaffrey    schedule 17.02.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Во-первых, вы можете использовать CTE для преобразования его в тип данных XML. Второй способ — через производную таблицу.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (SequentialNo INT PRIMARY KEY, Company VARCHAR(20), [DATA] VARCHAR(MAX));
INSERT INTO @tbl (SequentialNo, Company, [DATA])
VALUES
(1, 'ABC', '<ITEMS>
    <ITEM>
        <ID>1</ID>
        <NAME>John</NAME>
    </ITEM>
    <ITEM>
        <ID>5</ID>
        <NAME>James</NAME>
    </ITEM>
</ITEMS>');
-- DDL and sample data population, end

-- Method #1
-- CTE
;WITH rs AS
(
   SELECT *, TRY_CAST([DATA] AS XML) AS [xmldata]
   FROM @tbl
)
SELECT SequentialNo
    , Company
    , col.value('(ID/text())[1]','INT') AS ID
    , col.value('(NAME/text())[1]','VARCHAR(40)') AS [Name]
FROM rs AS tbl
    CROSS APPLY tbl.[xmldata].nodes('/ITEMS/ITEM') AS tab(col);

-- Method #2
-- Derived table
SELECT SequentialNo
    , Company
    , col.value('(ID/text())[1]','INT') AS ID
    , col.value('(NAME/text())[1]','VARCHAR(40)') AS [Name]
FROM (SELECT *, TRY_CAST([DATA] AS XML) AS [xmldata]
   FROM @tbl) AS tbl
    CROSS APPLY tbl.[xmldata].nodes('/ITEMS/ITEM') AS tab(col);

Выход

+--------------+---------+----+-------+
| SequentialNo | Company | ID | Name  |
+--------------+---------+----+-------+
|            1 | ABC     |  1 | John  |
|            1 | ABC     |  5 | James |
+--------------+---------+----+-------+
person Yitzhak Khabinsky    schedule 17.02.2020
comment
Спасибо помогло. Но есть ли возможное решение обойтись без CTE для преобразования varchar в xml? - person Gladstone Jaffrey; 18.02.2020
comment
Этот конкретный запрос будет использоваться таблицей. Табло при чтении данных не поддерживает CTE. Итак, есть ли способ использовать без CTE, например (подзапросы или что-то в этом роде) - person Gladstone Jaffrey; 18.02.2020
comment
Я обновил ответ вторым методом через производную таблицу. - person Yitzhak Khabinsky; 18.02.2020
comment
Приятно слышать, что предложенное решение работает на вас. Пожалуйста, отметьте это как ответ. Вам просто нужно пометить ответ как правильный (зеленая галочка). Щелкните зеленую галочку слева от ответа, который решил вашу проблему. Это помечает ответ как принятый - person Yitzhak Khabinsky; 18.02.2020