SQL Server 2005 XML到表

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

我正在使用经典的asp，我将varchar（ado数据类型）传递到proc中的xml数据列。

The xml looks as follows:

xml看起来如下：

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

我怎样才能在程序中这样做？ ADV连接中的adVarchar类型是否正常？

1 个解决方案

#1

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

所以你有一个存储过程，需要一个VARCHAR？好的，这样的东西会起作用：

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.

这将从您的单个标记中选择DocumentGUID和GraderFYC，然后遍历所有包含节点以获取其余数据。

#1