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

[reezaali@gmail.com]

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>VIN ET </CustomerID>


<CustomerID>Lil as</CustomerID>


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

CustomerID varchar(100) )

[Stu]

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

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>VIN ET </CustomerID>
</Customer>
<Customer>
<CustomerID>Lil as</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@hotmail.com]

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

Try this...


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

<CustomerID>VIN ET </CustomerID>


<CustomerID>Lil as</CustomerID>


</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_prepared ocument @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_removedo cument @iDoc

[reezaali@gmail.com]

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

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: Can someone tell me what's wrong with this code?

(reezaali@gmail .com) writes:[color=blue]
> 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...[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[reezaali@gmail.com]

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

Thank you...