xml insert - 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 2005 Forums

Transact-SQL (2005)

xml insert

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-12-15 : 06:21:42

Hello,
I am trying to use the following query so that I can insert the xml data into a table all in one go.
The problem seems to be that I do not get any data in the table.
can you see what i am doing wrong please?
Thanks

ALTER procedure [dbo].[uspVehicleImport]

@VehicleXML xml = null

as

set @VehicleXML =
'<Data>
<vehicle>
<Code>5434</Code>
<SID>3</SID>
<RID>1</RID>
<BID>2</BID>
<Amount>2765.43</Amount>
<gender>2</gender>
</vehicle>
<vehicle>
<Code>654</Code>
<SID>2</SID>
<RID>2</RID>
<BID>1</BID>
<Amount>600.43</Amount>
<gender>3</gender>
</vehicle>
<vehicle>
<Code>543</Code>
<SID>1</SID>
<RID>3</RID>
<BID>3</BID>
<Amount>99.99</Amount>
<gender>1</gender>
</vehicle>
</Data>'

print convert(varchar(1000), @VehicleXML)

select
cast(colx.query('data(Code) ') as varchar) as Code,
cast(colx.query('data(SID) ') as varchar) as SID,
cast(colx.query('data(RID) ') as varchar) as RID,
cast(colx.query('data(BID) ') as varchar) as BID,
cast(colx.query('data(Amount) ') as varchar) as Amount,
cast(colx.query('data(gender) ') as varchar) as gender
into
#tblVehicles
from
@VehicleXML.nodes('DocumentElement/mytable') AS Tabx(Colx)

select * from #tblVehicles

drop table #tblVehicles

--RESULT
Code SID RID BID Amount gender
5434 3 1 2 2765.43 2
654 2 2 1 600.43 3
543 1 3 3 99.99 1

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 06:24:19

Change 'DocumentElement/mytable' to 'Data/vehicle'.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-12-15 : 06:43:15

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources