none
Querying XML data in SQL Server 2005 RRS feed

  • Question

  • I am developing a C# .NET application that accesses XML stored in an XML column in a database.  I want to query the data, pulling an attribute from the XML and having it returned in a result set.  Something like:

    SELECT [Name], XMLData.query('data(/RootGeometry/@Tag)'FROM StudyXMLObject 
    WHERE StudyXMLOBject.StudyID = @StudyID  
     
    'Name' is a varchar and 'XMLData' and xmldata column.  'Tag' is an attribute in the RootGeometry element (root element).  This would return two columns of data, both strings.

    I can get the query to work just fine using MSSMSE but how do I do this with ADO.NET?  I usually use strongly typed datasets and add queries to the table adapter.  But the result from this query isn't a row from any table. 
    • Moved by Dan Benediktson Friday, March 13, 2009 10:11 PM Question is about LINQ to SQL (Moved from SQL Server Data Access to LINQ to SQL)
    Monday, February 23, 2009 9:17 PM

Answers

  • You could try simple datasets here.

    Sql Connection conn = new SqlConnection("blablabla");   
    SqlCommand cmd = new SqlCommand("SELECT [Name], XMLData.query('data(/RootGeometry/@Tag)') FROM StudyXMLObject WHERE StudyXMLOBject.StudyID = @StudyID");  
    cmd.Parameters ...  
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);   
    DataTable dt = new DataTable();   
    adapter.Fill(dt);   
     

    Thanks,
    Hooray
    • Proposed as answer by Hooray Hu Tuesday, March 10, 2009 2:54 AM
    • Unproposed as answer by Dan Benediktson Friday, March 13, 2009 10:11 PM
    • Marked as answer by johneanderson Sunday, March 15, 2009 4:37 PM
    Tuesday, March 10, 2009 2:54 AM
  • LINQ to SQL supports retrieving an XML column's data as a System.Xml.Linq.XElement instance but does not support running XQuery on that data. If you want to go through LINQ to SQL you will either have to use the DataContext.ExecuteQuery method to execute ad hoc SQL, or use a stored procedure that does what you need and execute it from your DataContext.
    • Marked as answer by johneanderson Sunday, March 15, 2009 4:36 PM
    Sunday, March 15, 2009 4:22 PM
    Answerer

All replies

  • You could try simple datasets here.

    Sql Connection conn = new SqlConnection("blablabla");   
    SqlCommand cmd = new SqlCommand("SELECT [Name], XMLData.query('data(/RootGeometry/@Tag)') FROM StudyXMLObject WHERE StudyXMLOBject.StudyID = @StudyID");  
    cmd.Parameters ...  
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);   
    DataTable dt = new DataTable();   
    adapter.Fill(dt);   
     

    Thanks,
    Hooray
    • Proposed as answer by Hooray Hu Tuesday, March 10, 2009 2:54 AM
    • Unproposed as answer by Dan Benediktson Friday, March 13, 2009 10:11 PM
    • Marked as answer by johneanderson Sunday, March 15, 2009 4:37 PM
    Tuesday, March 10, 2009 2:54 AM
  • Thanks for the reply.  I eventually stumbled into a solution similar to what you proposed and it works fine.

    Although my original question was how to do this with ADO.NET, which is what you answered, I am now contemplating using LINQ to SQL.  Using a DataContext subclass generated from a .dbml file, all tables are presented as classes and you can query by using LINQ syntax on the class members (columns).  But, I don't know how to embed XML queries into LINQ expressions.  Any ideas on that one?
    Tuesday, March 10, 2009 10:46 AM
  • LINQ to SQL supports retrieving an XML column's data as a System.Xml.Linq.XElement instance but does not support running XQuery on that data. If you want to go through LINQ to SQL you will either have to use the DataContext.ExecuteQuery method to execute ad hoc SQL, or use a stored procedure that does what you need and execute it from your DataContext.
    • Marked as answer by johneanderson Sunday, March 15, 2009 4:36 PM
    Sunday, March 15, 2009 4:22 PM
    Answerer
  • That's sort of what I figured.  Hmmm... I think I'll stick to ADO.NET and datasets.
    Sunday, March 15, 2009 4:36 PM
  • Glad that you've got the answer!

    Thanks,
    Hooray
    Sunday, March 22, 2009 11:23 AM