Импорт xml файла в базу данных MSSQl

Автор Hodogor На чтение 1 мин Просмотров 3.8к. Опубликовано 17.12.2020

Необходимо импортировать данные из XML-документа, в котором элементы хранятся следующим образом:

<result>
  <element>
    <id>14</id>
    <name>element1</name>
    <status>200</status>
  </element>
  <element>
    <id>17</id>
    <name>element17</name>
    <status>200</status>
  </element>
  <element>
    <id>23</id>
    <name>element23</name>
    <status>200</status>
  </element>
  <element>
    <id>32</id>
    <name>element32</name>
    <status>200</status>
  </element>
  <element>
    <id>51</id>
    <status>400</status>
    <errors>
      <element>
        <code>TestError</code>
        <description>Текст Ошибки</description>
      </element>
    </errors>
  </element>
</result>

<name>element1</name>

</element>

<name>element17</name>

</element>

<name>element23</name>

</element>

<name>element32</name>

</element>

<code>TestError</code>

<description>Текст Ошибки</description>

</element>

</errors>

</element>

</result>

Открываем xml файл используя оператор OPENROWSET и сохраняем в переменную @Xml

DECLARE @Xml xml;
SELECT @Xml = (SELECT * 
FROM OPENROWSET (BULK 'C:\test.xml', SINGLE_BLOB) as [xml]);

DECLARE @Xml xml;

SELECT @Xml = (SELECT *

FROM OPENROWSET (BULK 'C:\test.xml', SINGLE_BLOB) as [xml]);

Заполняем временную таблицу

select t.c.value('./id[1]', 'int')                          as ID
		  ,t.c.value('./name[1]', 'varchar(150)')		    as name
		  ,t.c.value('./status[1]', 'int')                  as status
		  ,t2.c2.value('./code[1]', 'varchar(150)')	        as code
		  ,t2.c2.value('./description[1]', 'varchar(150)')	as description	
	INTO #tmp_result
	from @Xml.nodes('result/element') T(c)
	OUTER APPLY t.c.nodes('errors/element') t2(c2);

select t.c.value('./id[1]', 'int') as ID

,t.c.value('./name[1]', 'varchar(150)') as name

,t.c.value('./status[1]', 'int') as status

,t2.c2.value('./code[1]', 'varchar(150)') as code

,t2.c2.value('./description[1]', 'varchar(150)') as description

INTO #tmp_result

from @Xml.nodes('result/element') T(c)

OUTER APPLY t.c.nodes('errors/element') t2(c2);

Результат загрузки

Добавить комментарий

Сергей 05.05.2022 в 14:14

что делать если при обращению к файлу SQL даёт ошибку
Cannot bulk load because the file «Путь к файлу.xml» could not be opened. Operating system error code 5(Отказано в доступе.)?

Ответить
1. Hodogor автор 05.05.2022 в 14:26
  
  Учетная запись службы SQL Server должна иметь разрешение на чтение этого файла.
  
  Ответить
Ирина 04.10.2022 в 15:35

Что делать, выходит ошибка «Microsoft XML Bulkload for SQL Server (16389) Error connecting to the data source.»?

Ответить
1. Hodogor автор 04.10.2022 в 16:29
  
  Эта ошибка выходит в SQL Server Management Studio?
  
  Ответить
  1. Ирина 05.10.2022 в 07:09
    
    Не выполняется пакетная загрузка XML на SQL-сервер. Select выполняется, а Update, Insert нет. Текстовые файлы загружаются нормально. Запускаем готовый файл загрузки.
    
    Ответить