SQL Server 2005 XML to table

Question

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:

<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?

Answer 1

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.