代码之家 › 专栏 › 技术社区 › Preet Sangha

使用nodes()将XML类型分解为存储过程中的行集

sql-server-2005 xml

Preet Sangha · 技术社区 · 14 年前

我有一个SP,我称之为下面的例子 (调用不是来自SQL,而是来自.net程序)

    -- run with a few grantees
    exec someproc 99999, '<grantees><grantee id="99"/><grantee id="100"/><grantee id="101"/></grantees>'
   -- takes about 1 sec with > 59s on xml decomp

或者也许

   -- run with lots of grantees (approx 2000)
   exec someproc 99999, '<grantees><grantee id="99"/><grantee id="100"/>....<grantee id="2001"/></grantees>'
   -- takes about 5 sec with > 4s on xml decomp

或者也许

   -- run with mega loads of grantees (approx 12000)
   exec someproc 99999, '<grantees><grantee id="99"/><grantee id="100"/>....<grantee id="12001"/></grantees>'
   -- takes about 1 min with > 59s on xml decomp

xml分解是最慢的部分

我很好奇,对于给定的输入集,我分解XML的方法是否是最理想的方法。我使用XML的标准只是简单地向SP传递一些整数,因此我们非常感谢您对更好方法的任何建议。

create procedure someproc(@id int, @users xml = '<grantees/>') as
begin
        -- decompose the users into a row set
        declare @allUsers table (
            id int
        )

        insert into @allUsers (id)
        select distinct grantee.value('@id', 'int') uno 
           from @users.nodes('/grantees/grantee') grantees(grantee)
           where isnull(grantee.value('@id', 'int'), 0) > 0

    select * from @allUsers

    -- other stuff happens
end

2 回复 | 直到 14 年前

KM. 14 年前

由于不能使用表参数,请尝试传入CSV sting,并让存储过程为您将其拆分为行。

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

你需要创建一个分割函数。这就是拆分函数的用法:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL 但是在SQLServer中有很多方法可以分割字符串,请参阅前面的链接,其中解释了每种方法的优缺点。

要使Numbers表方法工作,您需要执行一次性表设置,这将创建一个表 Numbers 包含从1到10000行的:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置完数字表后,创建此拆分函数:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(   ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO

CREATE PROCEDURE YourProcedure
(
    @CSV_Param   varchar(1000)
)
AS

--just an example of what you can do
UPDATE t
    SET Col1=...
    FROM dbo.FN_ListToTable(',',@CSV_Param) dt
        INNER JOIN TBL_USERS                 t ON  CAST(dt.value AS INT)=t.id

GO

只要从文章(CLR,loop,随便什么)中选择一个能处理大量csv的最佳字符串分割函数,就可以获得更好的性能。

TMN 14 年前

如果您所做的只是从元素集合中提取一个字段,那么只需将XML解析为字符串、将字段提取到数组中并将数组传递给SP或将值插入临时表并将表的名称传递给SP可能会更好,您没有数据库引擎解析XML。这种方法不是最灵活的,因此如果您需要对几种不同类型的元素执行此操作,则可能不合适。