如何使用Store Produre将Xml(通过Web服务返回)转换为表格... [英] How I Convert Xml(Return By Webservices) Into Table Using Store Produre...
本文介绍了如何使用Store Produre将Xml(通过Web服务返回)转换为表格...的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的工作是
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屋!
查看全文