如何使用 T-SQL 从 URL 读取 XML? [英] How can I to read a XML from a URL using T-SQL?

问题描述

在 url 中有 xml 文件:

Have xml file in url :

<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> 中的列值，想要添加 <强>买*1.9453到我的桌子上.怎么办?

Now want stored procedure where i can parse this xml file from url and update into columns values which is in <comment>sel*1.9488|buy*1.9453</comment> want add buy*1.9453 to my table. How do it ?

推荐答案

要从 URL 获取 XML，您需要执行以下操作:

To get the XML from a URL you need to do the following:

启用 Ole 自动化程序

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

然后从 url 获取 XML，(基于 此处)，下面创建一个临时表来存储值，以便您可以使用xpath 和子字符串.

Then to get the XML from a url, (answer based on an updated version from here), the following creates a temp table to store the value so you can then process the results using an xpath and substring.

这是一个使用 google maps xml 的工作示例，您需要根据您的特定要求更新 url 和 xpath.

This is a working example using a google maps xml, you will need to update the url and xpath to your specific requirements.

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

为了插入子字符串，您需要执行以下操作以返回管道后的所有内容并将其添加到您的表中:

In order to insert the substring you will need to do something like this to return everything after the pipe and add into your table:

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

这篇关于如何使用 T-SQL 从 URL 读取 XML?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！