sqlserver 处理输入的xml数据的两种方式

一段xml数据:

<DataTable><RowData><Id>-1</Id><name>name0</name><age>0</age></RowData><RowData><Id>-2</Id><name>name1</name><age>1</age></RowData><RowData><Id>-3</Id><name>name2</name><age>2</age></RowData><RowData><Id>-4</Id><name>name3</name><age>3</age></RowData><RowData><Id>-5</Id><name>name4</name><age>4</age></RowData><RowData><Id>-6</Id><name>name5</name><age>5</age></RowData><RowData><Id>-7</Id><name>name6</name><age>6</age></RowData><RowData><Id>-8</Id><name>name7</name><age>7</age></RowData><RowData><Id>-9</Id><name>name8</name><age>8</age></RowData><RowData><Id>-10</Id><name>name9</name><age>9</age></RowData></DataTable>

下面是处理这段xml数据的两种方式,后一种方式比前一个快.快多了

ALTER PROCEDURE [dbo].[InsertT1withXML]
@xml xml
AS
BEGIN

insert into t1(name,age)
select XmlTable.Data.value('(./name)[1]','nvarchar(50)') as Name,
     XmlTable.Data.value('(./age)[1]','int') as Age
from @xml.nodes('//DataTable/RowData') as XmlTable(Data)

end

 

ALTER PROCEDURE [dbo].[InsertT1withXML]
@xml xml
AS
BEGIN

declare @idoc int     
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
-- Execute a SELECT statement that uses the OPENXML rowset provider.

insert into t1(name,age)
SELECT    name,age
FROM       OPENXML (@idoc, '/DataTable/RowData',2)
WITH (id  int,
                  name varchar(20),
                  age int
                  )
END

 

转载于:https://www.cnblogs.com/brucepark/archive/2011/12/14/2287270.html