Измельчите XML-файл в таблицу SQL Server

Question

Я исследовал лучший способ уничтожить этот XML-файл и приблизился, но не полностью к тому, что я хочу.

Я использую SQL Server 2012 и Visual Studio 2012, хотя я предпочитаю использовать SQL Server.

Вот фрагмент типа данных XML, с которыми я работаю. Я не могу контролировать, как создается XML, так как он исходит от третьей стороны. В Reality ниже узла есть около 450 типов ответов, таких как ResponseID, Name, Status и т. Д. Я показываю только около десяти.

<xml>
    <Response>
        <ResponseID>R_a4yThVvKXzVyftz</ResponseID>
        <ResponseSet>Default Response Set</ResponseSet>
        <Name>Doe, John</Name>
        <ExternalDataReference>0</ExternalDataReference>
        <EmailAddress>[e-mail]</EmailAddress>
        <IPAddress>140.123.12.123</IPAddress>
        <Status>0</Status>
        <StartDate>2014-09-18 09:21:11</StartDate>
        <EndDate>2014-09-23 16:09:58</EndDate>
        <Finished>1</Finished>
            </Response>
</xml>

Я попробовал метод OPENROWSET, показанный на этом сайте

http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

Используя запрос как это:

SELECT
a1.value('(RESPONSEID/text())[1]', 'varchar(50)') as RESPONSEID,
a2.value('(RESPONSESET/text())[1]', 'varchar(50)') as RESPONSESET,
a3.value('(NAME/text())[1]', 'varchar(50)') as NAME
FROM  XmlSourceTable
CROSS APPLY XmlData.nodes('//Response') AS RESPONSEID(a1)
CROSS APPLY XmlData.nodes('//Response') AS RESPONSESET(a2)
CROSS APPLY XmlData.nodes('//Response') AS NAME(a3)

Я заставил это сработать один раз, но измельченный вывод повторял значения и не появлялся в желаемой форме таблицы, которая похожа на вывод ниже, хотя обратите внимание, что в действительности таблица очень широкая, всего 450 строк. Другая проблема связана с шириной, превышающей 255. Я не могу преобразовать это в.txt и импортировать его, хотя я бы предпочел использовать и уничтожать собственный XML, чтобы этот процесс можно было автоматизировать:

RESPONSEID  RESPONSESET NAME    EXTERNALDATAREFERENCE   EMAILADDRESS    IPADDRESS   STATUS  STARTDATE   ENDDATE
R_a4yThVvKXzVyftz   Default Response Set    Doe, John   1/1/2014    [e-mail]  123.12.123  0   9/18/2014 9:21  9/23/2014 16:09
R_06znwEis73yLsnX   NonDefault Response Set Doe, Jane   1/1/2014    [e-mail] 123.12.123  0   9/18/2014 5:29  9/29/2014 9:42
R_50HuB0jDFfI6hmZ   Response Set 1  Doe, Cindy  1/1/2014    [e-mail]  123.12.123  0   9/18/2014 17:21 10/1/2014 11:45

Я нашел это приложение

https://www.novixys.com/ExultSQLServer/

для уничтожения XML-файлов, которые создали единственную таблицу для Nodehowever, в дополнение к таблице ответов она создает таблицу для каждого узла ответа, в результате чего получается около 500 дополнительных таблиц. Также приложение стоит $250.

https://www.novixys.com/ExultSQLServer/

Answer 1

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

SELECT
  R.X.value('(ResponseID/text())[1]', 'varchar(50)') as RESPONSEID,
  R.X.value('(ResponseSet/text())[1]', 'varchar(50)') as RESPONSESET,
  R.X.value('(Name/text())[1]', 'varchar(50)') as NAME
FROM  XmlSourceTable
  CROSS APPLY XmlData.nodes('//Response') AS R(X)