Xml data type retrieve - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Xml data type retrieve

Author

Topic

madhan
Yak Posting Veteran

59 Posts

Posted - 2011-12-30 : 07:39:54

Hi - I have an xml document


<catalog>
  <book category="ITPro">
    <title>Windows Step By Step</title>
    <author>Bill Zack</author>
    <price>49.99</price>
  </book>
  <book category="Developer">
    <title>Developing ADO .NET</title>
    <author>Andrew Brust</author>
    <price>39.93</price>
  </book>
  <book category="ITPro">
    <title>Windows Cluster Server</title>
    <author>Stephen Forte</author>
    <price>59.99</price>
  </book>
</catalog>

In the sql server 2008 R2, I have created a table with xml data type. Now, trying to retrieve some columns from table using the below select statement.


select CustomerID,
o.value('Catalog[1]','varchar(30)') as title  
from tblXmlCustomers
CROSS APPLY CustomerXML.nodes('/catalog/book/title') x(o)

I just get title column filled with NULL.(No data) please help me to find out where I am wrong.

Thank you for your time and attention.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-30 : 08:06:31

Change 'Catalog[1]' to '.' as in

select CustomerID,
o.value('.','varchar(30)') as title  
from tblXmlCustomers
CROSS APPLY CustomerXML.nodes('/catalog/book/title') x(o)

madhan
Yak Posting Veteran

59 Posts

Posted - 2011-12-30 : 08:59:27

Thanks for your help, Sunita. If I change the .nodes('/catalog/book/') and would like to select attribute category,title,author,price. How would I do that? please excuse as I am very new to XQuery Xpath expressions.

Also, Could you please suggest me a good book o Xquery.

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-30 : 09:10:23

SQL XML follows the XPATH axes concept. When you specify a path in the cross apply, you are navigating to that node. Imagine yourself being at that node and then use XPATH expressions to move around.

So in your code, when you have "CROSS APPLY CustomerXML.nodes('/catalog/book/title') x(o)", you are at the title node. If you want to get the author and price, you will need to get the sibling axes. Usually it is more efficient to navigate to child nodes, so if you want to get all the details about the book node, navigate to only the book level and then pick up the category attribute and the child nodes of book.

I know you didn't post the question to hear me lecturing

, so I will get off my soapbox and show you what I meant via code:

SELECT
	CustomerId,
	o.value('./@category','varchar(30)') AS Category,
	o.value('./title[1]','varchar(255)') AS title,
	o.value('./author[1]','varchar(30)') AS author,
	o.value('./price[1]','float') AS price
FROM
	tblXmlCustomers
	CROSS APPLY CustomerXML.nodes('/catalog/book') x(o)