Выберите данные из XML файла в виде таблицы в TSQL

Может ли кто-нибудь показать мне какой-нибудь TSQL, чтобы использовать для запроса XML файла, как если бы это была таблица?

Ответы

Ответ 1

set @xmlData='<?xml version="1.0"?>
<ArrayOfSpangemansFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SpangemansFilter>
<FilterID>1219</FilterID>
<Name>Fred</Name>
<Code>510</Code>
<Department>N</Department>
<Number>305327</Number>
</SpangemansFilter>
<SpangemansFilter>
<FilterID>3578</FilterID>
<Name>Gary</Name>
<Code>001</Code>
<Department>B</Department>
<Number>0692690</Number>
</SpangemansFilter>
<SpangemansFilter>
<FilterID>3579</FilterID>
<Name>George</Name>
<Code>001</Code>
<Department>X</Department>
<Number>35933</Number>
</SpangemansFilter>
</ArrayOfSpangemansFilter>'


SELECT 
  ref.value('FilterID[1]', 'int') AS FilterID ,
  ref.value('Name[1]', 'NVARCHAR (10)') AS Name ,
  ref.value('Code[1]', 'NVARCHAR (10)') AS Code ,
  ref.value('Department[1]', 'NVARCHAR (3)') AS Department,
  ref.value('Number[1]', 'int') AS Number      
FROM @xmlData.nodes('/ArrayOfSpangemansFilter/SpangemansFilter') 
xmlData( ref )

Выдает:

FilterID    Name       Code       Department Number
----------- ---------- ---------- ---------- -----------
1219        Fred       510        N          305327
3578        Gary       001        B          692690
3579        George     001        X          35933

Примечание. [1] необходимо указать, что вы хотите выбрать первое значение последовательности, так как запрос может вернуть более одного совпадающего значения для каждой строки (представьте, что ваш XML содержит несколько фильтров для каждого SpangemansFilter).

Я думал, что это полезно знать, поэтому я Googled и прочитал много сообщений, пока не нашел этот.

UPDATE Для загрузки из файла:

DECLARE @xmlData XML
SET @xmlData = (
  SELECT * FROM OPENROWSET (
    BULK 'C:\yourfile.xml', SINGLE_CLOB
  ) AS xmlData
)

SELECT @xmlDatap >

Ответ 2

В моем случае - данные, которые меня интересовали, содержались в атрибутах node, а не в значениях. Ниже приведен пример того, как можно получить доступ к атрибутам.

DECLARE @xmlData XML
set @xmlData='<?xml version="1.0"?>
<ArrayOfSpangemansFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SpangemansFilter FilterID="1219" Name="Fred" Code="510" Department="N" Number="305327">
</SpangemansFilter>
<SpangemansFilter FilterID="3578" Name="Gary" Code="001" Department="B" Number="0692690">
</SpangemansFilter>
<SpangemansFilter FilterID="3579" Name="George" Code="001" Department="X" Number="35933">
</SpangemansFilter>
</ArrayOfSpangemansFilter>'


SELECT 
  ref.value('@FilterID', 'int') AS FilterID ,
  ref.value('@Name', 'NVARCHAR (10)') AS Name ,
  ref.value('@Code', 'NVARCHAR (10)') AS Code ,
  ref.value('@Department', 'NVARCHAR (3)') AS Department,
  ref.value('@Number', 'int') AS Number      
FROM @xmlData.nodes('/ArrayOfSpangemansFilter/SpangemansFilter') 
xmlData( ref )