August 21, 2009 at 1:11 pm
I saw a few samples on this topic while searching the forum, but I'm still not clear on this. For my example, I have the xml document:
DECLARE @x xml
SET @x =
'
'
(Note: The code block above shows 'data' and 'row' in upper case but they are actually lower case)
And I want to insert all of the 'ObjectId' values into one column of a table. I've tried two techniques.
First:
insert into [dbo].[tblTestIds]
select @x.query('data(/data/row/@ObjectId)').value('.','int') as ObjectId
which fails with this error:
Msg 245, Level 16, State 1, Procedure asi_spTest, Line 28
Conversion failed when converting the nvarchar value '1142 1143 1144' to data type int.
Second:
insert into [dbo].[tblTestIds]
select @x.value('(/data/row/@ObjectId)[1]','int') as ObjectId
which only gets the first ObjectId
How can I get each ObjectId from the xml into the table?
Thanks.
August 21, 2009 at 1:21 pm
I'd use OPENXML instead of Xquery.
DECLARE @x xml
SET @x =
'
'
DECLARE @iDoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @x
SELECT *
FROM OPENXML (@idoc, '/DATA/ROW',2)
WITH (
ObjectID int './@ObjectId'
)
EXEC sp_xml_removedocument @idoc
August 21, 2009 at 1:33 pm
Hi,
in order to be able to use the value of an xml variable in a SELECT statement you have to assign it to a "pseudo column" using a FROM clause.
In my example below I assigned the values from the xml nodes 'data/row' to the "pseudo column" c of the "pseudo table" T. It's a strange syntax but it works. 🙂
INSERT INTO [dbo].[tblTestIds]
SELECT
c.value ('@ObjectId[1]', 'int')
FROM @x.nodes('data/row') T(c)
August 21, 2009 at 1:34 pm
Thanks, that works. I'll need to read up on OPENXML
August 21, 2009 at 1:49 pm
khalprin (8/21/2009)
Thanks, that works. I'll need to read up on OPENXML
Well, you don't have to (as shown in my previous post).
Before looking deeper into OPENXML instead of XQuery I'd recommend to review some articles comparing the two.
Three links I'd recommend to look into:
http://articles.techrepublic.com.com/5100-10878_11-6140404.html, and
You might want to do more research if you'd like.
Maybe John can add some "pro openxml". Mine are all cons... 😉
August 21, 2009 at 1:57 pm
Don't use OPENXML! Use XQuery. See:
August 21, 2009 at 2:00 pm
Maybe John can add some "pro openxml". Mine are all cons...
No, I sure can't. I've seen some articles over the last 6 months or so comparing the two on small and large documents. Since then, I've used a mix-match of both methods. There are things in XQuery, like modifiying XML nodes and values, that you simply cannot do in OPENXML. For the most part, I've stuck with OPENXML due to its relative ease of use.
Your link from Michael Coles is an eye opener. I did not realize the memory ramifications of OPENXML. Thanks for sharing.
August 21, 2009 at 2:04 pm
khalprin (8/21/2009)
Thanks, that works. I'll need to read up on OPENXML
See post 774356 for a list of Microsoft white papers on XML to read.
http://www.sqlservercentral.com/Forums/FindPost774356.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply