Openxml выбирает данные из сложного xml с префиксом
Структура XML и ожидаемый результат приведены ниже, я могу просмотреть все данные, но не могу выбрать элементы xml из xpath, поскольку узел item имеет префикс «a:»
Я пробовал следующее, но никто не работает:
№1:
Set @path = N'/root/JSON/schData/ESU/item[1]/ESU_Bl_0'
SELECT * from openxml (@hDoc, @path, 2)
with
(
ID nvarchar(200)
,Item_0_1 nvarchar(200)
,Item_0_2 nvarchar(200)
)
№2:
Set @path = N'/root/JSON/schData/ESU/a:item/ESU_Bl_0'
SELECT * from openxml (@hDoc, @path, 2)
with
(
ID nvarchar(200)
,Item_0_1 nvarchar(200)
,Item_0_2 nvarchar(200)
)
№3:
Set @path = N'/root/JSON/schData/ESU//*:item/ESU_Bl_0'
SELECT * from openxml (@hDoc, @path, 2)
with
(
ID nvarchar(200)
,Item_0_1 nvarchar(200)
,Item_0_2 nvarchar(200)
)
№4:
Set @path = N'/root/JSON/schData/ESU/a[1]/ESU_Bl_0'
SELECT * from openxml (@hDoc, @path, 2)
with
(
ID nvarchar(200)
,Item_0_1 nvarchar(200)
,Item_0_2 nvarchar(200)
)
Пожалуйста, помогите своим опытом, спасибо !!
ОБНОВЛЕНИЕ: образец xml, как указано в комментарии -
<root type = "object">
<JSON type = "object">
<schData type = "object">
<ESU type = "object">
<a:item xmlns:a = "item" item = "0" type = "object">
<ESU_Bl_0 type = "object">
<ID type = "number">1</ID>
<Item_0_1 type = "string">A</Item_0_1>
<Item_0_2 type = "string">B</Item_0_2>
</ESU_Bl_0>
<ESU_Bl_1 type = "object">
<ID type = "number">2</ID>
<Item_1_1 type = "string">C</Item_1_1>
<Item_1_2 type = "string">D</Item_1_2>
</ESU_Bl_1>
</a:item>
<a:item xmlns:a = "item" item = "1" type = "object">
<ESU_Bl_0 type = "object">
<ID type = "number">3</ID>
<Item_0_1 type = "string">E</Item_0_1>
<Item_0_2 type = "string">F</Item_0_2>
</ESU_Bl_0>
<ESU_Bl_1 type = "object">
<ID type = "number">4</ID>
<Item_1_1 type = "string">G</Item_1_1>
<Item_1_2 type = "string">H</Item_1_2>
</ESU_Bl_1>
</a:item>
</ESU>
</schData>
</JSON>
</root>
ОБНОВЛЯТЬ:
Решение, предложенное Ицхаком, подходит для статического запроса, но когда я пытаюсь динамически ввести переменную Counter, я получаю следующую ошибку.
Line 41 [Batch Start Line 0]
The argument 1 of the XML data type method "nodes" must be a string literal.
Вот как я пытаюсь:
;WITH XMLNAMESPACES('item' AS a)
SELECT c.value('(ID/text())[1]', 'INT') AS ID
, c.value('(Item_0_1/text())[1]', 'VARCHAR(20)') AS Item_0_1
, c.value('(Item_0_2/text())[1]', 'VARCHAR(20)') AS Item_0_2
FROM @xml.nodes('/root/JSON/schData/ESU/a:item[@item = "'+@Counter+'"]/ESU_Bl_0') AS t(c);
Задавая вопрос, вы должны предоставить минимальный воспроизводимый пример: (1) DDL и выборочное заполнение данных, т. е. таблицы CREATE плюс операторы INSERT T-SQL. (2) Что вам нужно сделать, т. е. логика и ваш код попытаются реализовать ее в T-SQL. (3) Желаемый результат, основанный на примерных данных в # 1 выше. (4) Ваша версия SQL Server (SELECT @@version;). Все в рамках вопроса, без изображений.
Привет, @DavidBrowne-Microsoft, добавил XML. Спасибо.
Ваш XML неправильно сформирован. "Ошибка синтаксического анализа в Untitled2.xml: ожидаемый конец тега Item_1_1" Пожалуйста, исправьте.
@YitzhakKhabinsky, исправлено, извините, xml был слишком большим, при уменьшении я допустил эту ошибку.
В соответствии с руководством по вопросам, пожалуйста, не публикуйте изображения кода, данных, сообщений об ошибках и т. д. - скопируйте или введите текст в вопрос. Пожалуйста, зарезервируйте использование изображений для диаграмм или демонстрации ошибок рендеринга, вещей, которые невозможно точно описать с помощью текста.
Ответы 2
Пожалуйста, попробуйте следующее решение.
Собственный Microsoft OPENXML() и его компаньоны sp_xml_preparedocument и sp_xml_removedocument сохранены только для обратной совместимости с устаревшим SQL.
Server 2000. Их использование сведено к очень небольшому количеству крайних случаев.
Начиная с SQL Server 2005 и далее, настоятельно рекомендуется переписать ваш SQL и переключиться на XQuery.
SQL
DECLARE @xml XML =
N'<root type = "object">
<JSON type = "object">
<schData type = "object">
<ESU type = "object">
<a:item xmlns:a = "item" item = "0" type = "object">
<ESU_Bl_0 type = "object">
<ID type = "number">1</ID>
<Item_0_1 type = "string">A</Item_0_1>
<Item_0_2 type = "string">B</Item_0_2>
</ESU_Bl_0>
<ESU_Bl_1 type = "object">
<ID type = "number">2</ID>
<Item_1_1 type = "string">C</Item_1_1>
<Item_1_2 type = "string">D</Item_1_2>
</ESU_Bl_1>
</a:item>
<a:item xmlns:a = "item" item = "1" type = "object">
<ESU_Bl_0 type = "object">
<ID type = "number">3</ID>
<Item_0_1 type = "string">E</Item_0_1>
<Item_0_2 type = "string">F</Item_0_2>
</ESU_Bl_0>
<ESU_Bl_1 type = "object">
<ID type = "number">4</ID>
<Item_1_1 type = "string">G</Item_1_1>
<Item_1_2 type = "string">H</Item_1_2>
</ESU_Bl_1>
</a:item>
</ESU>
</schData>
</JSON>
</root>';
;WITH XMLNAMESPACES('item' AS a)
SELECT c.value('(ID/text())[1]', 'INT') AS ID
, c.value('(Item_0_1/text())[1]', 'VARCHAR(20)') AS Item_0_1
, c.value('(Item_0_2/text())[1]', 'VARCHAR(20)') AS Item_0_2
FROM @xml.nodes('/root/JSON/schData/ESU/a:item[@item = "0"]/ESU_Bl_0') AS t(c);
SQL №2
Метод XQuery .nodes() принимает только литеральный параметр. Но его легко параметризовать через sql:variable() или sql:column(). Пожалуйста, смотрите ниже.
DECLARE @Counter INT = 0;
;WITH XMLNAMESPACES('item' AS a)
SELECT c.value('(ID/text())[1]', 'INT') AS ID
, c.value('(Item_0_1/text())[1]', 'VARCHAR(20)') AS Item_0_1
, c.value('(Item_0_2/text())[1]', 'VARCHAR(20)') AS Item_0_2
FROM @xml.nodes('/root/JSON/schData/ESU/a:item[@item=sql:variable("@Counter")]/ESU_Bl_0') AS t(c);
Выход
Привет @Yitzhak, я столкнулся с проблемой при динамизации вашего решения, обновил вопрос новой проблемой,
Я обновил ответ с помощью SQL # 2.
Итак, чтобы сделать его динамичным, вот что вы можете сделать с решением Ицхака:
;WITH XMLNAMESPACES('item' AS a)
SELECT c.value('(ID/text())[1]', 'INT') AS ID
, c.value('(Item_0_1/text())[1]', 'VARCHAR(20)') AS Item_0_1
, c.value('(Item_0_2/text())[1]', 'VARCHAR(20)') AS Item_0_2
FROM @xml.nodes('/root/JSON/schData/ESU/a:item[@item=sql:variable("@Counter")]/ESU_Bl_0') AS t(c);
Таким образом счетчик будет динамически вставлен.
Отправьте xml в виде текста, а не изображения.