SQL Server Performance Forum – Threads Archive

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!

---

]]>