Hi Mike,
I am unsure if I understood correctly your requirements (there isn't any
attribute inside your XPath query, isn't it?). However, below you can
find an XPath/SQL query which retrieves both the title and the author
text value for each book element:
SELECT T.C.value('title[1]', 'nvarchar(max)') AS Title,
T.C.value('author[1]', 'nvarchar(max)') AS Author
FROM @xml.nodes('/books/book') AS T(C)
HTH
--
Efran Cobisi
http://www.cobisi.com
Mike Andrews wrote:
This might be Off Topic but here goes:
I have an XPATH query question regarding element names and how to retrieve
them in SQL Server 2005.
Specifically given this xml document or something similar:
<books>
<book>
<title>The Way Things Are.</title>
<author>Irene R. Gibson</author>
</book>
<book>
<title>The Way Things Were.</title>
<author>Kari D. Bednarz</author>
</book>
</books>
I want to be able to specify that I want all "title"s and all "author"s
within the same query.
Here's what I have so far and I can't seem to find any examples of anything
dealing with elements vs. attributes:
SELECT T.C.value('.[text()]', 'varchar(255)') AS NodeText
FROM @xml.nodes('/books/book/*') AS T(C)
Now, I realize that the text() method only produces the text. However I've
not been able to find a way to query the node text based on the name of the
node.
Any help would be appreciated.
Thanks,
Mike
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
