如何使用T-SQL从URL读取XML？

Question

在URL中有XML文件：

<response>
<sum>0</sum>
<result>0</result>
<comment>sel*1.9488|buy*1.9453</comment>
</response>

现在我想要一个存储过程，可以解析来自url的xml文件，并将其中的<comment>sel*1.9488|buy*1.9453</comment>中的值更新到相应列中。我想要将buy*1.9453添加到我的表格中。如何实现？

- GeoVIP

Answer 1

从URL获取XML需要执行以下步骤：

启用Ole自动化程序

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

然后，要从URL获取XML（基于here的更新版本进行回答），以下代码创建一个临时表来存储该值，以便您可以使用xpath和substring处理结果。

这是使用Google Maps XML的工作示例，您需要根据自己的特定要求更新URL和xpath。

USE tempdb
GO

IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
CREATE TABLE #xml ( yourXML XML )
GO

DECLARE @URL VARCHAR(8000) 

DECLARE @QS varchar(50)

-- & or ? depending if there are other query strings
-- Use this for when there is other query strings:
SELECT @QS = '&date='+convert(varchar(25),getdate(),126)
-- Use this for when there is NO other query strings:
-- SELECT @QS = '?date='+convert(varchar(25),getdate(),126)
SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-65.598409&sensor=false'  + @QS

DECLARE @Response varchar(8000)
DECLARE @XML xml
DECLARE @Obj int 
DECLARE @Result int 
DECLARE @HTTPStatus int 
DECLARE @ErrorMsg varchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 

INSERT #xml ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 

SELECT  yourXML.value('(//GeocodeResponse/status)[1]','VARCHAR(MAX)') from #xml

为了插入子字符串，您需要像这样返回管道后的所有内容并将其添加到您的表中：

INSERT tableDestination (valueDestination)
SELECT  substring(yourXML.value('(//response/comment)[1]','VARCHAR(MAX)'),charindex('|',yourXML.value('(//response/comment)[1]','VARCHAR(MAX)'),1)+1,len(yourXML.value('(//response/comment)','VARCHAR(MAX)'))) from #xml

- Matthew Warman

---

好的，已更新答案以包括子字符串部分，以获取管道后面的所有内容。 - Matthew Warman

@Mattew Warman，我将我的URL写入@url，并将SELECT yourXML.value('(//GeocodeResponse/status)[1]','VARCHAR(MAX)') from #xml更改为SELECT yourXML.value('(//response/comment)[1]','VARCHAR(MAX)') from #xml，当我执行存储过程时，选择写入访问被拒绝。 - GeoVIP

您能通过在运行代码的SQL服务器上使用IE访问URL吗？可能需要将“'MSXML2.XMLHttp'”更新为“'MSXML2.ServerXMLHTTP.6.0'”，因为错误听起来类似于此链接。 - Matthew Warman

@Mattew Warman 你好，我有一个问题。URL中的XML文件已更新，现在是<comment>sel*1.9588|buy*1.9653</comment>，但我的程序解析的是旧的XML注释。为什么程序没有刷新呢？ - GeoVIP

@GeoVIP 看起来存在缓存问题，这篇帖子可能会有所帮助。有两个选项：查看IE设置或向URL的查询字符串添加一个随机值。我已经更新了上面的代码以添加日期到查询字符串中。这样能解决问题吗？ - Matthew Warman

Answer 2

像这样的东西怎么样？

DECLARE @xml XML = 
'<response>
<sum>0</sum>
<result>0</result>
<comment>sel*1.9488|buy*1.9453</comment>
</response>'

SELECT  @xml.value('(//response/comment)[1]','VARCHAR(MAX)')

来自value()方法(xml数据类型)

对XML执行XQuery并返回SQL类型的值。 此方法返回标量值。

通常使用此方法从存储在xml类型列、参数或变量中的xml实例中提取值。通过这种方式，您可以指定将XML数据与非XML列中的数据组合或比较的SELECT查询。

SQL Fiddle演示

- Adriaan Stander

---

谢谢，它有效了，但我想从URL解析它，例如：http://mywebserver/libreria.xml，怎么做？ - GeoVIP