Retrieving specific XML data in SQL Server
I have a question regarding SQL Server. I have a table with an ntext datatype column where I am storing XML data. My question is: How do I write a user-defined function to retrieve a particular xml attribute in that column?
Software/Hardware used:
Software/Hardware used:
ASKED:
June 23, 2008 6:57 PM
UPDATED:
June 23, 2008 9:48 PM
Answer Wiki:
You would do this by using the OPENXML query. Below is some sample code which will simulate doing this.
<pre>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.*/</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.