how to split a xml format data into row column format in sql server 2008 using stored procedure

Question

0.00/5 (No votes)

See more:

what i have done till now is as :

SQL

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 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>

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

Posted 25-Aug-13 23:46pm

Rambo_Raja

Updated 26-Aug-13 1:35am

Aarti Meswania

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Accepted Answer · 2013-08-26T01:31:00

Use this .. it will help...

SQL

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:

SQL

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

Hope this helps...