在 sql server 2005 中迭代 XML 数据 [英] iterating XML data in sql server 2005

问题描述

我有一个接受的存储过程

I have a storedprocedure which accepts

@MissingRecordsXML NTEXT

它包含 XML 记录.现在，其他开发人员使用游标从 OPENXML 中获取行，然后将所有业务规则应用于每一行，然后将每一行插入到表中.

It contains XML records. Now someother developer has used a cursor to fetch rows from OPENXML and then has applied all the business rules on each row and after that each row is inserted in a table.

我想知道在 SQL Server 中 foreach XML 数据的最佳方法是什么.我必须提高这个 SP 的性能，因为当有多条记录时它真的很慢.游标已经声明为只读.请帮助

I want to know which is the best way to foreach XML data in SQL Server.I have to enhance performance of this SP as it is really slow when there are multiple records.Cursors are already declared as Read-Only.Please help

从 XML 填充游标的代码是:-

Code which fills the cursor from XML is:-

exec sp_xml_preparedocument @hDocSEC OUTPUT,@MissingRecordsXML  

DECLARE SEC_UPDATE CURSOR  FOR  
SELECT MissingColumn,TableName,PhysicalColName,Grantor_Grantee  
FROM OPENXML (@hDocSEC,'MissingDS/MissingTable',2)  
WITH (MissingColumn VARCHAR(1000),TableName VARCHAR(100),  
PhysicalColName VARCHAR(100),Grantor_Grantee VARCHAR(100) )  
OPEN SEC_UPDATE  
FETCH NEXT FROM SEC_UPDATE  
INTO @MissingColumn,@TableName,@ActualColumnName,@Grantor_Grantee 

推荐答案

假设您的示例 XML 如下所示:

Assuming your sample XML looks something like this:

<MissingDS>
  <MissingTable>
    <MissingColumn>abc</MissingColumn>
    <TableName>tblMyTable</TableName>
    <PhysicalColName>table_abc</PhysicalColName>
    <Grantor_Grantee>nobody</Grantor_Grantee>
  </MissingTable>
  <MissingTable>
    <MissingColumn>xyu</MissingColumn>
    <TableName>tblMyTable2</TableName>
    <PhysicalColName>table_xyz</PhysicalColName>
    <Grantor_Grantee>nobody2</Grantor_Grantee>
  </MissingTable>
</MissingDS>

然后您可以像这样使用新的 SQL Server 2005 XQuery 支持来解析它:

Then you could parse this with the new SQL Server 2005 XQuery support like this:

DECLARE @MissingXML XML
SET @MissingXML = CAST(@MissingRecordsXML AS XML)

SELECT
    Missing.Rec.value('(MissingColumn)[1]', 'varchar(1000)') AS 'MissingColumn',
    Missing.Rec.value('(TableName)[1]', 'varchar(100)') AS 'TableName',
    Missing.Rec.value('(PhysicalColName)[1]', 'varchar(100)') AS 'Physical',
    Missing.Rec.value('(Grantor_Grantee)[1]', 'varchar(100)') AS 'Grantor_Grantee'
FROM
    @MissingXML.nodes('/MissingDS/MissingTable') AS Missing(Rec)

当然，如果你可以选择它，你也可以很容易地将相同的数据行插入到表中:

Of course, if you can SELECT it, you can also INSERT that same data rows into a table quite easily:

INSERT INTO 
  dbo.MissingDSTable(MissingColumn, TableName, PhysicalColName, Grantor_Grantee)
    SELECT
       Missing.Rec.value('(MissingColumn)[1]', 'varchar(1000)') AS 'MissingColumn',
       Missing.Rec.value('(TableName)[1]', 'varchar(100)') AS 'TableName',
       Missing.Rec.value('(PhysicalColName)[1]', 'varchar(100)') AS 'Physical',
       Missing.Rec.value('(Grantor_Grantee)[1]', 'varchar(100)') AS 'Grantor_Grantee'
    FROM
       @MissingXML.nodes('/MissingDS/MissingTable') AS Missing(Rec)

希望对你有所帮助

马克

这篇关于在 sql server 2005 中迭代 XML 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！