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

[reezaali]

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

[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

[markc600]

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

[reezaali]

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

[Erland Sommarskog]

(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

[reezaali]

Thank you...