SQL Server 2005 XML to table

I’m using classic asp and I’m passing in a varchar (ado data type) into an xml data column in a proc.

The xml looks as follows:

  • SQL default schema resolution in Stored Procedure
  • UTC time stamp to Datetime in Sql server
  • Joining two tables in SQL Server when one table has huge data and other has few data
  • Adding node to xml output using for xml path
  • How can I query SQL Server database for the nearest store in a visitor's neighborhood?
  • Sql to update login id's dynamically based on count
  • <DocumentCriteria> 
       <Document documentGUID="{B49654E7-9AF2-4B89-AF8F-5146F7CD4911}" graderFYC="5907"> 
          <criterion cnumber="1" rank="3"/>
          <criterion cnumber="3" rank="3"/>
       </Document>
    </DocumentCriteria>'
    

    I’d like to have a table that looks like this:

    DocumentGUID|GraderFYC|Cnumber|Rank
    {guid}      | 5907    | 1     | 3
    {guid}      | 5907    | 3     | 3
    

    How can I do this within a procedure? Is the adVarchar type okay in the ADO connection?

    One Solution collect form web for “SQL Server 2005 XML to table”

    So you have a stored proc that takes a VARCHAR? OK, something like this will work:

    ALTER PROCEDURE dbo.YourProcedure(@data VARCHAR(2000))
    AS BEGIN
    
       DECLARE @XmlContent XML
       SET @XmlContent = CAST(@data AS XML)
    
       INSERT INTO dbo.YourTable(DocumentGUID, GraderFYC, Cnumber, Rank)
          SELECT
             @XmlContent.value('(/DocumentCriteria/Document/@documentGUID)[1]', 'Varchar(50)'),
             @XmlContent.value('(/DocumentCriteria/Document/@graderFYC)[1]', 'int'),
             Doc.Crit.value('(@cnumber)[1]', 'int') AS 'CNumber',
             Doc.Crit.value('(@rank)[1]', 'int') AS 'Rank'
          FROM
             @XmlContent.nodes('/DocumentCriteria/Document/criterion') AS Doc(Crit)
    END
    

    This selects the DocumentGUID and GraderFYC from your single <Document> tag and then iterates over all contains <criterion> nodes to grab the rest of the data.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.