XML into Table format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

XML into Table format

Author

Topic

MIB
Starting Member

4 Posts

Posted - 2002-09-23 : 23:56:11

Hi
tableA has a field which contain XML string as below:
<Body>
<PaymentsNo>3</PaymentsNo>
<Payment><PaymentNo>0</PaymentNo><PayAmt>2399.04</PayAmt></Payment>
<Payment><PaymentNo>1</PaymentNo><PayAmt>236.05</PayAmt></Payment>
<Payment><PaymentNo>2</PaymentNo><PayAmt>509.15</PayAmt></Payment>
</Body>

I want to make a table as below
PaymentNo Amount
0 2399.04
1 236.05
2 509.15

Thank you if anyone can help me.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-09-24 : 04:57:16

Take a look at the OPENXML function in BOL.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-24 : 08:40:04

 
declare @xml varchar(1000)
DECLARE @idoc int

/*
Could equally be from a table e.g.

select @xml = xml_text from mytable where id=1

*/

set @xml=
'<Body> 
<PaymentsNo>3</PaymentsNo> 
<Payment><PaymentNo>0</PaymentNo><PayAmt>2399.04</PayAmt></Payment> 
<Payment><PaymentNo>1</PaymentNo><PayAmt>236.05</PayAmt></Payment> 
<Payment><PaymentNo>2</PaymentNo><PayAmt>509.15</PayAmt></Payment> 
</Body>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

SELECT    *
FROM       OPENXML (@idoc, '/Body/Payment',2)
            WITH (PaymentNo  int,
                  PayAmt     numeric(9,2))

EXEC sp_xml_removedocument @idoc

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources