How I Convert Xml(Return By Webservices) Into Table Using Store Produre...

See more:

ASP.NET

webservice

, +

my work is
1.Access Data From webservices method,
2 then convert this Xml into table,

but my problem is Xml file(return by Webservices) is not well formed, it uses many tags,

so how i convert this xml into table,
Below is my Store procedure..

ALTER PROCEDURE [dbo].[proc_CallWebServices]
	
	@nicuid varchar(200)='185'
AS
Declare @Obj int
Declare @ValorDeRegreso int
Declare @sUrl Varchar(200)
Declare @response Varchar(8000)
Declare @hr int
Declare @src Varchar(255)
Declare @desc Varchar(255)
DECLARE @xml XML;


Set @sUrl='XXX/EmployeeBankNicuidWise?nicuid='+@nicuid;

Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT;
Exec sp_OAMethod @obj, 'Open', NULL, 'get',@sUrl,false
Exec sp_OAMethod @obj, 'send'
Exec sp_OAGetProperty @obj, 'responseText', @response OUT
 
Select @response [response]

SET @xml = @response;
SELECT @xml;
 
-- Now Convert XML into Table
Create Table #TempStatus(NICUID INT,BankActNo NVARCHAR(50))

 INSERT INTO #TempStatus
SELECT Tbl.Col.value('@NICUID', 'INT'),
Tbl.Col.value('@BankActNo', 'NVARCHAR(50)')
FROM @xml.nodes('MySampleTable/') Tbl(Col)

 SELECT * FROM #TempStatus
 drop table #TempStatus

And XML File Looking Like this:

<DataTable xmlns="XXXXXXXXXX">
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="NewDataSet">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="MySampleTable" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="MySampleTable">
<xs:complexType>
<xs:sequence>
<xs:element name="NICUID" type="xs:int" minOccurs="0"/>
<xs:element name="BankActNo" type="xs:string" minOccurs="0"/>

</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">
<MySampleTable diffgr:id="MySampleTable1" msdata:rowOrder="0">
<NICUID>1855864</NICUID>
<BankActNo>2222</BankActNo>
</MySampleTable>
<MySampleTable diffgr:id="MySampleTable1" msdata:rowOrder="0">
<NICUID>1855864</NICUID>
<BankActNo>2222</BankActNo>
</MySampleTable>

So any one plz help me.. the simple XML to Table conversion is:

DECLARE @tempTable TABLE (
userId INT,
userName NVARCHAR(50)
--password NVARCHAR(50)
)

Create Table #TempStatus(userId INT,userName NVARCHAR(50),password NVARCHAR(50))
DECLARE @xml XML
SET @xml='
<row userId="67" userName="Kenny1" password="1234" />
<row userId="80" userName="Kenny2" password="5678" />'
INSERT INTO #TempStatus
SELECT Tbl.Col.value('@userId', 'INT'),
Tbl.Col.value('@userName', 'NVARCHAR(50)'),
Tbl.Col.value('@password', 'NVARCHAR(50)')
FROM @xml.nodes('row') Tbl(Col)
--See the table
SELECT * FROM #TempStatus
DROP Table #TempStatus

in my condition Xml file tag is different so plz help me...