June 21, 2007 at 9:34 am
Hello,
I am using SQL 2005, I loaded an XML document in an XML data type column. I wrote a query:
SELECT
XMLData.query('/products/product_data/price')
FROM tbProductFeed
This works great but it will return a result in the following format:
How can I get it to just return 120.00? I am not sure if I need to modify the XPath query (newbie) or change a setting in the database, etc.
Your help would be greatly appreciated on this matter.
Thank you,
Tekguy
June 22, 2007 at 1:01 am
Although it's difficult to see from your post I assume you meant to say that it returns the result in the format
<price>120.00</price>
and you actually wanted just the value. In which case you can change your query to
SELECT
XMLData.value('(/products/product_data/price)[1]', 'smallmoney')
FROM tbProductFeed
substituting whatever datatype you want to return.
June 22, 2007 at 7:22 am
Thanks noggin this is great, But lets say I have 5 products in the XML code how can I return back all 5 prices (5 records)? It seems like i would have to create a sql variable in the xquery, or is there another way?
June 22, 2007 at 8:14 am
Tekguy,
I've knocked up some demo xml (taking a guess at what your data looks like) and a query that gets the data you need.
HTH
DECLARE @xml xml
SET @xml = N'
<products>
<product_data>
<id>123</id>
<name>Product Number 1</name>
<price>215.00</price>
</product_data>
<product_data>
<id>456</id>
<name>Product Number 2</name>
<price>741.00</price>
</product_data>
<product_data>
<id>789</id>
<name>Product Number 3</name>
<price>379.00</price>
</product_data>
</products>'
SELECTproduct.value('id[1]', 'int') AS ProductID,
product.value('name[1]', 'varchar(50)') AS ProductName,
product.value('price[1]', 'smallmoney') AS ProductPrice
FROM@xml.nodes('products/product_data') AS Products(product)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply