Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    103

    Unanswered: SQL Server 2005 & XML

    New to using XML, so please be kind:

    Anyone have a quick and easy suggestion, example, and/or link on how to query data from an XML document stored as an XML type field in SQL Server 2005?

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @doc_handle int
    DECLARE @xml xml
    SET @xml ='<root>
                 <parentnode attribute1="1">
                   <childnode>
                     A
                   </childnode>
                   <childnode>
                     B
                   </childnode>
                 </parentnode>
                 <parentnode attribute1="2">
                   <childnode>
                     C
                   </childnode>
                 </parentnode>
                 <parentnode attribute="3" />
               </root>'
    
    BEGIN TRY
      --Prepare xml document
      EXEC sp_xml_preparedocument @doc_handle OUTPUT, @xml
        
      SELECT *
      FROM   OPENXML (@doc_handle, '/root/parentnode/childnode')
    END TRY
    
    BEGIN CATCH
      --Always unprepare document
      EXEC sp_xml_removedocument @doc_handle
    END CATCH
    http://msdn.microsoft.com/en-us/library/ms187897.aspx

    Books Online is your friend.
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •