sql - 如何使用 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> 中的列值想加 购买*1.9453 到我的 table 。怎么办？

Answer 1

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

启用 Ole Automation Procedures

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

然后为了从 url 获取 XML，(答案基于来自 here 的更新版本)，下面创建一个临时表来存储值，以便您可以使用 xpath 和子字符串处理结果。

这是一个使用 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