i am getting response from asp.net web service as mentioned in below format . I want to convert this in a table format that is age in age column and date in date column. I have asked this question earlier but then it was a string now getting o/p in xml what should i do?
getting response output in @response in this format :
<?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.I am getting @response as the above mentioned form but unable to use the way i am doing . can anybody help me.
Use a string split function of your choice and split on ^ first and | second. After that you can use pivot to get your data in columns.
declare @XML xml = '<?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>';
with xmlnamespaces(default 'http://abc.org/')
select P.age,
P.Date
from (
select S1.ID,
left(S2.Item, charindex('=', S2.Item) - 1) ColName,
stuff(S2.Item, 1, charindex('=', S2.Item), '') Value
from SplitString(@XML.value('string[1]', 'varchar(max)'), '^') as S1
cross apply SplitString(S1.Item, '|') as S2
) as X
pivot
(
min(X.Value)
for X.ColName in (age, Date)
) as P;
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다