469,579 Members | 1,899 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Can someone tell me what's wrong with this code?

Hi All

I keep getting back VINET and not Lilas...can someone point me in the
right direction?

Thanks a lot
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>

<CustomerID>VINET </CustomerID>
<CustomerID>Lilas</CustomerID>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT',2)
WITH (

CustomerID varchar(100) )

Jul 23 '05 #1
5 1216
Stu
I'm not an expert on SQL Server and XML, but it seems to me that
OPENXML is very picky about the XML form that it will accept; can you
modify your XML statement to read like this?

SET @doc ='
<ROOT>
<Customer>
<CustomerID>VINET </CustomerID>
</Customer>
<Customer>
<CustomerID>Lilas</CustomerID>
</Customer>
</ROOT>'

and then your OPENXML to statement to read like this:

SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',2)
WITH (
CustomerID varchar(100) )
Don't know why it works, but it does.

Stu

Jul 23 '05 #2
Try this...
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>

<CustomerID>VINET </CustomerID>
<CustomerID>Lilas</CustomerID>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/CustomerID',2)
WITH (

CustomerID varchar(100) '.')

EXEC sp_xml_removedocument @iDoc

Jul 23 '05 #3
Thank you Mark that works great!!!!
Can you explain to my why '.' needed to be included? I can't seem to
locate it in Books online...

Thanks again

Jul 23 '05 #4
(re******@gmail.com) writes:
Thank you Mark that works great!!!!
Can you explain to my why '.' needed to be included? I can't seem to
locate it in Books online...


Books Online says about col-pattern:

Is an optional, general XPath pattern that describes how the XML nodes
should be mapped to the columns. If the ColPattern is not specified, the
default mapping (attribute-centric or element-centric mapping as
specified by flags) takes place.

'.' maps to the current node, which is /Root/CustomerID.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
Thank you...

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Helmut Giese | last post: by
40 posts views Thread by aslamhenry | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.