将XML数据存储在表中

I have a XML data in format:

我有格式的XML数据:

<Display>
 <StoreCode>1234</StoreCode>
 <TerminalID>02</TerminalID>
 <TrnsNo>0123456789</TrnsNo>
 <Date>03-05-2013</Date>
 <Time>11:30</Time>
 <Vdtls>
  <VID>VVB0000015</VID>
  <VID>VVB0000016</VID>
 </Vdtls>
</Display>

I have stored this xml data in a column of a table. Now I want to parse this XML and store it in another table that has structure.

我已将此xml数据存储在表的列中。现在我想解析这个XML并将其存储在另一个具有结构的表中。

 CREATE TABLE [dbo].[temp_XMLreceivedData]
 (
[StoreCode] [int] NULL,
[TerminalId] [int] NULL,
[TransactionNo] [varchar](10) NULL,
[RequestDate] [date] NULL,
[RequestTime] [char](5) NULL,
[VoucherDetails] [varchar](10) NULL
 )

How to achieve this?

怎么做到这一点?

2 个解决方案

#1

Try the following Code

请尝试以下代码

insert into temp_XMLreceivedData
select 
a.b.value('(StoreCode/node())[1]','VARCHAR(10)') ,
a.b.value('(TerminalID/node())[1]','VARCHAR(100)'),
a.b.value('(TrnsNo/node())[1]','VARCHAR(100)') ,
a.b.value('(Date/node())[1]','VARCHAR(100)') ,
a.b.value('(Time/node())[1]','VARCHAR(100)') ,
a.b.value('(Vdtls/VID/node())[1]','VARCHAR(100)') 
from @XmlTbl.nodes('Display') as a(b)

#2

I thing the whole code is as follows

我的整个代码如下

declare @XmlTbl xml

select top(1) @XmlTbl=cast(cast(xmldata as nText) as xml) from test_xml

insert into temp_XMLreceivedData
select 
a.b.value('(StoreCode/node())[1]','VARCHAR(10)') ,
a.b.value('(TerminalID/node())[1]','VARCHAR(100)'),
a.b.value('(TrnsNo/node())[1]','VARCHAR(100)') ,
a.b.value('(Date/node())[1]','VARCHAR(100)') ,
a.b.value('(Time/node())[1]','VARCHAR(100)') ,
a.b.value('(Vdtls/VID/node())[1]','VARCHAR(100)') 
from @XmlTbl.nodes('Display') as a(b)

this code works for only one data. looping the table if you insert all data of data

此代码仅适用于一个数据。如果插入所有数据数据,则循环表

#1