Преобразуем XML в таблицу - применение XQuery к данным типа xml.

Tsql теория > Преобразуем XML в таблицу - применение XQuery к данным типа xml.
10.04.2017 17:38:37


Наиболее часто встречающиеся слова в статье:

[//mai/shema"] [xmlns="http] [xml_reader] [/ArrayOfTOOItemRow/TOOItemRow] [nvarchar] [''declare] [namespace] [s="http] [variable] [''nvarchar]


Статья:

Преобразуем XML в таблицу

OPENXML  процедура sp_xml_preparedocument не работает с XML данными, которым назначена схема.

Альтернативным вариантом является применение XQuery к данным типа xml. XQuery начался поддерживаются с MS SQL Server 2005.

DECLARE @xml_reader xml =N''<?xml version="1.0" encoding="utf-16"?>
<ArrayOfTOOItemRow xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<TOOItemRow>
<Period xmlns="http://mai/shema">2016-04-01</Period>
<ProjectID xmlns="http://mai/shema"> EEL </ProjectID>
<ItemID xmlns="http://mai/shema"> 16 </ItemID>
<ProfitCenterID xmlns="http://mai/shema"> 2N </ProfitCenterID>
<SystemID xmlns="http://mai/shema" />
<SummaRub xmlns="http://mai/shema">19500</SummaRub>
</TOOItemRow>
<TOOItemRow>
<Period xmlns="http://mai/shema">2016-06-01</Period>
<ProjectID xmlns="http://mai/shema"> GTI </ProjectID>
<ItemID xmlns="http://mai/shema"> 16 </ItemID>
<ProfitCenterID xmlns="http://mai/shema"> 12 </ProfitCenterID>
<SystemID xmlns="http://mai/shema" />
<SummaRub xmlns="http://mai/shema">137609</SummaRub>
</TOOItemRow>
</ArrayOfTOOItemRow>
'';


-- запуск считывания с xml файла на диске файловой системы ------------------------------------

--SELECT @xml_reader = CONVERT(xml, BulkColumn, 2)
--FROM OPENROWSET(BULK ''C:\tmp\spb1c8srv.kisbcc.CRMService.Results.xml'', SINGLE_BLOB) as x

--------------------------------------------------------------------------------------------------

DECLARE @xml_Table_reader table(OrderID nvarchar(25), Name nvarchar(25),ItemID nvarchar(25),ProfitCenterID nvarchar(25),SystemID nvarchar(25),SummaRub nvarchar(25) );
DECLARE @count int = @xml_reader.value(''count(/ArrayOfTOOItemRow/TOOItemRow)''
, ''int'');

DECLARE @i int = 1;

WHILE @i <= @count BEGIN
INSERT @xml_Table_reader(OrderID, Name,ItemID,ProfitCenterID,SystemID,SummaRub)
SELECT @xml_reader.value(''declare namespace s="http://mai/shema";
(/ArrayOfTOOItemRow/TOOItemRow[sql:variable("@i")]/s:Period)[1]''
, ''nvarchar(25)'')
, @xml_reader.value(''declare namespace s="http://mai/shema";
(/ArrayOfTOOItemRow/TOOItemRow[sql:variable("@i")]/s:ProjectID)[1]''
, ''nvarchar(25)'')
, @xml_reader.value(''declare namespace s="http://mai/shema";
(/ArrayOfTOOItemRow/TOOItemRow[sql:variable("@i")]/s:ItemID)[1]''
, ''nvarchar(25)'')
, @xml_reader.value(''declare namespace s="http://mai/shema";
(/ArrayOfTOOItemRow/TOOItemRow[sql:variable("@i")]/s:ProfitCenterID)[1]''
, ''nvarchar(25)'')
, @xml_reader.value(''declare namespace s="http://mai/shema";
(/ArrayOfTOOItemRow/TOOItemRow[sql:variable("@i")]/s:SystemID)[1]''
, ''nvarchar(25)'')
, @xml_reader.value(''declare namespace s="http://mai/shema";
(/ArrayOfTOOItemRow/TOOItemRow[sql:variable("@i")]/s:SummaRub)[1]''
, ''nvarchar(25)'')

SET @i += 1;
END

SELECT * FROM @xml_Table_reader