Mrdenny
46795 pts. | Jun 23 2008 9:48PM GMT
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.



CREATE TABLE #t
(ID INT,
Data NTEXT)
INSERT INTO #t
SELECT 1, '<root>
<customer id="1" FirstName="Fred">
<order id="1" Amount="2.95" Items="1">
<item id="1" SKU="329" Cost="2.95" />
</order>
</customer>
</root>'
INSERT INTO #t
SELECT 2, '<root>
<customer id="2" FirstName="Bob">
<order id="1" Amount="4.95" Items="1">
<item id="1" SKU="654" Cost="4.95" />
</order>
</customer>
</root>'
DECLARE @XML NVARCHAR(4000)
SELECT @XML = Data
FROM #t
WHERE Id = 2
/*The Code from here down would be inside your function*/
DECLARE @hDoc INT
exec sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, '/root/customer/order/item')
WITH (CustomerId INT '../../@id',
FirstName NVARCHAR(40) '../../@FirstName',
OrderId INT '../@id',
ItemId INT '@id',
SKU INT '@SKU',
ItemCost NUMERIC(5,2) '@Cost')
exec sp_xml_removedocument @hDoc
drop table #t
/*You would then return the needed value(s) from the function via either a single value or a table variable.*/


Mrdenny
46795 pts. | Jun 23 2008 9:48PM GMT
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
