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) )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|||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|||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|||(reezaali@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you...
