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:

  • How To Set Limit Table Row SQL?
  • How to find SQL Server running port?
  • Using C# & SMO, how do I add a backup device to a SQL Server?
  • SQL Server tough Query
  • How do I generate row number without using rownum() in sql server
  • Function-based indexes in SQL Server
  • <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.