Re: How to read Value from XML String and Insert to a table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

HI Plamen,

This works as expected. Can you explain us brief on the logic.
Also this will work only on SQL Server 2005 rite?

Why is this master..spt_values used?

Thanks in advance

Plamen Ratchev wrote:
Here is one method. Involves parsing XML, pivoting, and splitting
delimited list.

DECLARE @xmlMTPData XML;

SET @xmlMTPData =
N'<mtp>
<item>
<CName>Name</CName>
<CValue>Andrey</CValue>
</item>
<item>
<CName>Location</CName>
<CValue>Zurich</CValue>
</item>
<item>
<CName>Quantity</CName>
<CValue>500;200;300;600</CValue>
</item>
</mtp>';

SELECT n + 1 - LEN(REPLACE(LEFT(Quantity, n), ';', '')) AS id,
Name,
Location,
SUBSTRING(Quantity, n,
CHARINDEX(';',
Quantity + ';', n) - n) AS Quantity
FROM (
SELECT Name, Location, Quantity
FROM (
SELECT T.item.query('CName').value('.', 'VARCHAR(50)') AS cname,
T.item.query('CValue').value('.', 'VARCHAR(200)') AS cvalue
FROM @xmlMTPData.nodes('mtp/item') AS T(item)) AS X
PIVOT
(MAX(cvalue) FOR cname IN (Name, Location, Quantity)) AS P) AS D
JOIN (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 250) AS Nums(n)
ON SUBSTRING(';' + Quantity, n, 1) = ';'
AND n < LEN(Quantity) + 1;

/*

Results:

id Name Location Quantity
---- -------- ---------- ---------
1 Andrey Zurich 500
2 Andrey Zurich 200
3 Andrey Zurich 300
4 Andrey Zurich 600

*/


--
Message posted via http://www.sqlmonster.com

.