typed xml in sql - is this possible?

I've a table with a typed XML field. Typical XML format is:

<root>
  <users>
      <user name="John Doe" />
      <user name="Alexander" />
   </users>
   <apps>
      <app name="Office 2010" />
      <app name="SQL Server 2005" />
   <apps>
</root>

Assuming this table has 25 rows with different values in the users and apps elements, is there a way to:

  1. Retrieve the list of all users in the /root/users element for all the records.
  2. Retrieve the list of all users + application for all the records.

SQL on this line works but gives me only first user name.

SELECT xtbl.col1.value('(user/@name)[1]', 'varchar(100)') 
FROM mytable
     CROSS APPLY xmlcol.nodes('/root/users') AS xtbl(col1)

Answers


The nodes function is selecting one row per users, but you're looking for one row per user. Try:

SELECT xtbl.col1.value('(@name)[1]', 'varchar(100)') 
FROM mytable
     CROSS APPLY xmlcol.nodes('/root/users/user') AS xtbl(col1)

Need Your Help

How to fnd UUID by programming in iphone?

iphone objective-c cocoa-touch ios4 uuid

I want to fetch UUID of iphone by programming and also want to store it in my database.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.