XML parameter passing (into a stored proc) and subsequent parsing

Question

Post reply

XML parameter passing (into a stored proc) and subsequent parsing

Rob Priestley

SSC Rookie

Points: 32
More actions
September 22, 2010 at 7:47 am

#226738

Hi,
I have been googling and trying to solve this most of the morning.
I have the following SQL;
DECLARE @XMLHandle INT
DECLARE @stdXML XML
SET @stdXML = '<standards><StandardID>8</StandardID><StandardRatingID>1</StandardRatingID><StandardID>13</StandardID><StandardRatingID>26</StandardRatingID></standards>'
DECLARE @TmpStds TABLE
(
StdID INT,
StdRatingID INT
)
EXEC sp_xml_preparedocument @XmlHandle output, @stdXML
INSERT INTO @TmpStds
SELECT StdID, StdRatingID
FROM OPENXML (@XmlHandle, '/standards', 1)
WITH (StdID int 'StandardID',
StdRatingID int 'StandardRatingID')
SELECT * FROM @TmpStds
It returns 8 and 1.
What I want it to do is return:
8, 1
13,26
ie return all data and insert into my temp table - any ideas what I am missing/doing wrong?
Many thanks.
Rob.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rob Priestley SSC Rookie Points: 32 More actions · Answer 1

OK - this seems to work OK now.

DECLARE @XMLHandle INT

DECLARE @stdXML XML

SET @stdXML =

'<Standards>

</Standards>'

DECLARE @TmpStds TABLE

(

StdID INT,

StdRatingID INT

)

EXEC sp_xml_preparedocument @XmlHandle output, @stdXML

INSERT INTO @TmpStds

SELECT *

FROM OPENXML (@XMLHandle, '/Standards/Standard',1)

WITH (StandardID varchar(10),

StandardRatingID varchar(20))

SELECT * FROM @TmpStds

Any better suggestions most welcome.

Rob.

LutzM SSC Guru Points: 107049 More actions · Answer 2

I'd rather use XQuery instead of OpenXML since it provides more flexibility when dealing with xml data.

DECLARE @stdXML XML

SET @stdXML =

'<Standards>

<Standard StandardID="8" StandardRatingID="1"></Standard>

<Standard StandardID="13" StandardRatingID="26"></Standard>

</Standards>'

DECLARE @TmpStds TABLE

(

StdID INT,

StdRatingID INT

)

INSERT INTO @TmpStds

SELECT

T.c.value('@StandardID[1]','INT'),

T.c.value('@StandardRatingID[1]','INT')

FROM @stdXML.nodes('Standards/Standard') T(c)

SELECT *

FROM @TmpStds

As a great resource for XQuery samples you could have a look at Jacob Sebastian's blog.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Rob Priestley SSC Rookie Points: 32 More actions · Answer 3

Hi, thanks for your response.

I like your suggested format also, so I will use this instead.

Many Thanks.

Rob.