如何使用Store Produre将Xml（通过Web服务返回）转换为表格... [英] How I Convert Xml(Return By Webservices) Into Table Using Store Produre...

问题描述

我的工作是

1.访问数据来自webservices方法，

2然后将此Xml转换为表格，



但我的问题是Xml文件（由Webservices返回）格式不正确，它使用了很多标签，



所以我如何将这个xml转换成表格，

以下是我的商店程序..

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

和XML文件看起来像这样：

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>

所以任何人都可以帮助我..简单的XML到表的转换是：

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

我的情况Xml文件标签不同所以plz帮我...

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

推荐答案

你必须为它编写代码。

解析XML并将其转换为数据表或直接将其推入数据库。



不知道，但是这个视频 [ ^ ]可能会有帮助。

You have to write the code for it.
Parse the XML and convert it into data tables or directly push it into the database.

Don't know, but this video[^] might help.

这篇关于如何使用Store Produre将Xml（通过Web服务返回）转换为表格...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！