将XML文件分解为SQL Server表

问题描述：

我已经研究了广泛地分解这个xml文件的最佳方法，并且已经接近但并非完全符合我的要求。将XML文件分解为SQL Server表

我正在使用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>[email protected]</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>

我试着在这个网站

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

所示。采用这样的查询OPENROWSET方法：

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 [email protected] 123.12.123 0 9/18/2014 9:21 9/23/2014 16:09 
R_06znwEis73yLsnX NonDefault Response Set Doe, Jane 1/1/2014 [email protected] 123.12.123 0 9/18/2014 5:29 9/29/2014 9:42 
R_50HuB0jDFfI6hmZ Response Set 1 Doe, Cindy 1/1/2014 [email protected] 123.12.123 0 9/18/2014 17:21 10/1/2014 11:45

我发现这个应用程序

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

撕碎它创建一个表的Nodehowever除了响应表是每个并导致约500额外的表每个响应节点创建一个表XML文件。另外，申请费250 $ ..

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

你能澄清你的问题吗？另外，它与ExultSQLServer – Raptor

没有关系，我提到Exult，因为它确实提供了一种成功分解XML文件的方法，尽管目前这不是我需要的正确解决方案。 –

答

你并不需要添加一个交叉申请要提取每个值。一个就足够了。

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)

完美地工作。 –

将XML文件分解为SQL Server表

相关推荐