XML duplicate tags issue

I'm having an issue pulling values from tags that have the same name as per below...

This returns 2 values for customerID (GOOD!) Notice that it is attribute based!

DECLARE @idoc int
DECLARE @doc varchar(8000)
Set @doc = '
<ROOT>
<Customer CustomerID="VINET" ></Customer>
<Customer CustomerID="LILAS" ></Customer>
</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/Customer',1)
WITH (CustomerID varchar(10))

-------------------------------------------------------

BUT THIS RETURNS ONLY 1 VALUE (VERY BAD - ELEMENT BASED!)

DECLARE @idoc int
DECLARE @doc varchar(8000)
Set @doc = '
<ROOT>
<Customer>
<CustomerID>VINET</CustomerID>
<CustomerID>VIghds</CustomerID>
</Customer>
</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/Customer',2)
WITH (CustomerID varchar(10))


WHY!?!? AND HOW CAN I GET 2 VALUES BACK ELEMENT BASED?!?

Thanks a million.

Keep in mind that using element based setups result in different hierarchical structures. Using dot notation, the difference might be something like this from what you have above.

root.Customer.attributes["CustomerID"]
vs
root.Customer.CustomerID.childNodes[0].value

Give the following a shot.

DECLARE @idoc int
DECLARE @doc varchar(8000)
Set @doc = '
<ROOT>
<Customer>
<CustomerID CID="VINET" />
<CustomerID CID="LILAS" />
</Customer>
</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/Customer/CustomerID',2)
WITH (CID varchar(10) '@CID')

John

In case someone else needs this, here is the solution:

DECLARE @idoc int
DECLARE @doc varchar(8000)
Set @doc = '<root><CustomerID>VINET</CustomerID><CustomerID>VIghds</CustomerID><CustomerID>VIghdfdss</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, '',2)

This will return a nice able structure will all node information:
0NULL1rootNULLNULLNULLNULLNULL
201CustomerIDNULLNULLNULLNULLNULL
523#textNULLNULLNULLNULLVINET
301CustomerIDNULLNULLNULL2NULL
633#textNULLNULLNULLNULLVIghds
401CustomerIDNULLNULLNULL3NULL
743#textNULLNULLNULLNULLVIghdfdss


SWEET!