如何使用存储过程将xml格式数据拆分为sql server 2008中的行列格式

问题描述

我到目前为止所做的是：

what i have done till now is as :

ALTER proc [dbo].[WebServiceCall]      
as        
begin        
declare @sUrl varchar(4000),@obj int,@response varchar(800)  
exec sp_OAMethod @obj,'Open',NULL,'GET', @sUrl, false        
exec sp_OAMethod @obj,'send'        
exec sp_OAGetProperty @obj,'responseText', @response OUT        
select  @response [response]        
exec sp_OADestroy @obj         
set @sUrl ='http://10.130.7.147/Eservice/SSISService.asmx/HelloMoon' 
declare @data xml 
declare @data1 xml 
select @data = @response 
declare @str nvarchar(max) 
select @str = cast(@data as Nvarchar(max))
--select @data = @response 
select @str  = replace(@str, '=', '="')
select @str = replace(@str, '|', '" ')
select @str = replace(@str, '^', '"/>'

gettinng响应为：

gettinng response as:

<?xml version="1.0" encoding="utf-8"?>  <string xmlns="http://abc.org/">age=7|Date=15/04/2006^age=5|Date=15/04/2008</string>

如何使用存储过程将其拆分为sql server 2008中的行列格式。谢谢..

How split this into row column format in sql server 2008 using Stored procedure.Thanks..

推荐答案

DECLARE @str VARCHAR(2000), @strXML VARCHAR(8000),@XML XML
Set @str	= 'age=7|Date=15/04/2006^age=5|Date=15/04/2008'
Set @str	= '<table><row><col>' + @str + '</col></row></table>'
Set @str	= REPLACE(@str,'|','</col><col>')
Set @str	= REPLACE(@str,'^','</col></row> <row><col>')
set @str	= REPLACE(@str,'Date=','')
Set @strXML = REPLACE(@str,'age=','')
Set @XML	= CAST(@strXML AS XML)

--@XML Ouput will be like this:  
--<table>
--  <row>
--    <col>7</col>
--    <col>15/04/2006</col>
--  </row>
--  <row>
--    <col>5</col>
--    <col>15/04/2008</col>
--  </row>
--</table>

SELECT line.col.value('col[1]', 'varchar(1000)') AS Age,
	   line.col.value('col[2]', 'varchar(1000)') AS Date
FROM @XML.nodes('/table/row') AS line(col) 

输出：

Output:

Age	   Date
---      ----------
7	  15/04/2006
5	  15/04/2008

希望这会有所帮助......

Hope this helps...

这篇关于如何使用存储过程将xml格式数据拆分为sql server 2008中的行列格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！