Can someone tell me whats 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) )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...

Can someone help!

Hi All
I need some help. I have a requirement wherein I need to know the time
difference that elapsed between the creation of the defect and the
resolution of the defect.
If there are holidays(Saturday & Sunday) between the time, then it
should not be included.
Is there a way I can do that by using a SQL stored procedure written in
SQL 2005.Any help is highly appreciated.
Thank you very much
Ami.The use of a calendar table would prove to be quite useful for this and
other date related issues. See:
Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519
Datetime -How to count the number of business days
http://www.aspfaq.com/show.asp?id=2453
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ami" <amutha55@.gmail.com> wrote in message
news:1157857041.111774.64780@.m73g2000cwd.googlegroups.com...
> Hi All
> I need some help. I have a requirement wherein I need to know the time
> difference that elapsed between the creation of the defect and the
> resolution of the defect.
> If there are holidays(Saturday & Sunday) between the time, then it
> should not be included.
> Is there a way I can do that by using a SQL stored procedure written in
> SQL 2005.Any help is highly appreciated.
> Thank you very much
> Ami.
>