OPENXML - 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

SQL Server Development (2000)

OPENXML

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-04-14 : 16:38:23

I'm new to parsing xml in sql. i'm writing a stored procedure that will insert some values into a table based on the xml parameter that i pass it. all of my variables are getting set to null. i know i'm doing something incorrect here. just not sure what. any ideas?

Here's my sample XML that i pass to the stored procedure:


<?xml version="1.0"?>
<product>
	<orderitemid>some text</orderitemid>
	<displayname>some text</displayname>
	<developername>some text</developername>
	<esrbvalue>nr</esrbvalue>
	<esrbdescriptors></esrbdescriptors>
	<snippet>some text</snippet>
	<shortdescription>some text</shortdescription>
	<description>some text</description>
	<merchandise>False</merchandise>
	<leaderboard>False</leaderboard>
	<downloadsize>123456</downloadsize>
	<active>True</active>
</product>

Here's the code snippet from my stored procedure:


declare @orderItemId		varchar(64)
declare @displayName		nvarchar(64)
declare @esrbDescriptors	nvarchar(128)
declare @snippet		nvarchar(40)
declare @shortDescription	nvarchar(256)
declare @description		nvarchar(2048)
declare @merchandise		bit
declare @leaderboard		bit
declare @downloadSize		int
declare @active			bit
declare @esrbId 		int
declare @developerId 		int
declare @idoc 			int

exec sp_xml_preparedocument @idoc output, @productXml

select	@orderItemId = OrderItemId, 
	@displayName = DisplayName, 
	@esrbDescriptors = EsrbDescriptors, 
	@snippet = Snippet, 
	@shortDescription = ShortDescription, 
	@description = [Description], 
	@merchandise = Merchandise, 
	@leaderboard = Leaderboard, 
	@downloadSize = DownloadSize, 
	@active = Active
from	openxml	(@idoc, '/product/', 2)
with	(	
	orderItemId		varchar(64),
	displayName		nvarchar(64),
	esrbDescriptors		nvarchar(128),
	snippet			nvarchar(40),
	shortDescription	nvarchar(256),
	[description]		nvarchar(2048),
	merchandise		bit,
	leaderboard		bit,
	downloadSize		int,
	active			bit
)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-14 : 17:20:23

with (
orderitemid varchar(64) 'orderitemid' ,
displayName nvarchar(64) 'displayName' ,

...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-04-14 : 17:45:08

no go.. still null

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-04-14 : 17:49:44

for the record, i've tried "openxml(@idoc, '/product', 2)" as well -- removing the trailing slash. i've tried surrounding it in a root element also. i've tried this same thing with an attribute centric schema and xml. same result.. null values

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-14 : 18:30:46

Sorry - yes you need to remove the trailing /.
Here's something that does work - build on that

declare @productXml varchar(8000)

select @productXml =
'<?xml version="1.0"?>
<product>
<orderitemid>some text</orderitemid>
<displayname>some text</displayname>
<developername>some text</developername>
<esrbvalue>nr</esrbvalue>
<esrbdescriptors></esrbdescriptors>
<snippet>some text</snippet>
<shortdescription>some text</shortdescription>
<description>some text</description>
<merchandise>False</merchandise>
<leaderboard>False</leaderboard>
<downloadsize>123456</downloadsize>
<active>True</active>
</product>'

declare @idoc int
exec sp_xml_preparedocument @idoc output, @productXml

select *
from openxml (@idoc, '/product',2)
with (
orderitemid varchar(64) 'orderitemid' ,
displayName nvarchar(64) 'displayname'
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-14 : 18:33:01

You also have to have the case correct when referencing the tag.
i.e. 'orderitemid' not 'orderItemId'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-04-14 : 18:37:06

that was it! i had the wrong case, as xml was case sensative.

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-04-14 : 19:08:21

i still had a problem with data conversion, since in the xml, boolean values are defined as "TRUE" or "FALSE".. i was able to resolve that with the following solution. this now works perfectly:


declare @orderItemId		varchar(64)
declare @displayName		nvarchar(64)
declare @esrbDescriptors	nvarchar(128)
declare @snippet		nvarchar(40)
declare @shortDescription	nvarchar(256)
declare @description		nvarchar(2048)
declare @merchandise		bit
declare @leaderboard		bit
declare @downloadSize		int
declare @active			bit
declare @esrbId 		int
declare @developerId 		int
declare @idoc 			int

exec sp_xml_preparedocument @idoc output, @productXml

select	@orderItemId = orderitemid,
	@displayName = displayname,
	@esrbDescriptors = esrbdescriptors,
	@snippet = snippet,
	@shortDescription = shortdescription,
	@description = [description],
	@merchandise = case upper(merchandise) when N'TRUE' then 1 else 0 end,
	@leaderboard = case upper(leaderboard) when N'TRUE' then 1 else 0 end,
	@downloadSize = downloadsize,
	@active	= case upper(active) when N'TRUE' then 1 else 0 end
from	openxml	(@idoc, '/product', 2)
with	(	
		orderitemid		varchar(64),
		displayname		nvarchar(64),
		esrbdescriptors		nvarchar(128),
		snippet			nvarchar(40),
		shortdescription	nvarchar(256),
		[description]		nvarchar(2048),
		merchandise		varchar(5),
		leaderboard		varchar(5),
		downloadsize		int,
		active			varchar(5)
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources