locked
Storing XML in SQL 2005 RRS feed

  • Question

  • How to store XML data in SQL Server 2005?
    Friday, October 12, 2007 5:55 PM

All replies

  • Creating a Table to Store XML

    To give you a feel for how this stuff actually works, I'll build and use a simple table with an untyped XML column (that is, an XML column that does not have a specific schema associated with it). To create the table, I can use the CREATE TABLE statement:

    
    CREATE TABLE Books
     (BookID int identity PRIMARY KEY,
      BookDetails xml)
    

    That's all there is to it! When you want XML, just use the xml data type, which is a fundamental part of the product. Inserting data is simple as well:

    
    INSERT INTO Books (BookDetails)
      VALUES ('Mike GunderloySybex')
    INSERT INTO Books (BookDetails)
      VALUES ('Mike GunderloySybex')
    INSERT INTO Books (BookDetails)
      VALUES ('Mike GunderloyQue')
    INSERT INTO Books (BookDetails)
      VALUES ('Mike GunderloySybex')
    

     

     

    Using XQuery With XML Columns

     

    Of course, you can use a regular column-based SELECT statement if you just want to get the entire contents of an XML column back:

    
    SELECT * FROM Books WHERE BookID = 1
    
    BookID      BookDetails
    ----------- --------------------------------------------------------
    1           Mike GunderloySybex
    
    (1 row(s) affected)
    
    How could you retrieve only the <author> tags from the Books table:
    
    SELECT BookDetails::query('book/author') FROM Books
    
    -------------------------------
    Mike Gunderloy
    Mike Gunderloy
    Mike Gunderloy
    Mike Gunderloy
    
    (4 row(s) affected)
    

    The query() method returns its results as an XML fragment. You can also use the closely-allied value() method to return the results as scalar values instead of untyped XML:

    
    SELECT BookDetails::value('book/title', 'nvarchar(max)') FROM Books
    
    ---------------------------
    Mastering SQL Server 2000
    MCAD 70-306 Exam Cram
    MCAD 70-305 Training Guide
    ADO and ADO.NET Programming
    
    (4 row(s) affected)

    The value() method requires you to specify the data type that you want to get back (think of it as a sort of CAST statement for XQuery. Using this method returns just the contents of the XML, without the associated tags.

    Friday, October 12, 2007 6:43 PM
  • Saturday, October 13, 2007 12:51 AM