RATE THIS ANSWER
0
Click to Vote:

•   0
•  0

• 

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.*/

Last Answered: Jun 23 2008  9:48 PM GMT by Mrdenny   46795 pts.