代码之家 › 专栏 › 技术社区 › ESV Alconja

SQL Server XML查询建议

xquery xpath tsql xml sql-server

ESV Alconja · 技术社区 · 16 年前

我正在编写一个用户定义函数,从SQL Server中的XML列中提取值,该列表示一个简单的字符串键值对字典。到目前为止,我让它工作的唯一方法似乎过于复杂。你有什么简单的建议或提示吗 DictValue 功能在下面?

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DictValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[DictValue]
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableWithXmlColumn]') AND type in (N'U'))
DROP TABLE [dbo].[TableWithXmlColumn]
go

create table TableWithXmlColumn (
    Id int identity primary key
    ,Dict xml
    )
go

create function DictValue(
    @id int
    ,@key nvarchar(max)
    ) returns nvarchar(max) as begin

    declare @d xml -- string Dictionary
    select @d = Dict from TableWithXmlColumn where Id = @id
    declare @value xml
    select 
            @value = d.Pair.value('data(.)', 'nvarchar(max)')
        from 
            @d.nodes('/StringDictionary/Pair') as d(Pair)
        where 
            @key = d.Pair.value('./@Key', 'nvarchar(max)')

    return convert(nvarchar(max), @value)
    end
go

declare @xmlId int
insert TableWithXmlColumn (Dict) values (
    N'<?xml version="1.0" encoding="utf-16"?>
    <StringDictionary>
      <Pair Key="color">red</Pair>
      <Pair Key="count">123</Pair>
    </StringDictionary>')
set @xmlId = scope_identity()

select 
    dbo.DictValue(@xmlId, 'color') as color
    ,dbo.DictValue(@xmlId, 'count') as [count]

3 回复 | 直到 13 年前

ewbi 16 年前

我发现下面的变量绑定XQuery方法更容易理解:

create function DictValue(
  @id int,
  @key nvarchar(max)
) 
returns nvarchar(max) as 
begin
  declare @value nvarchar(max)
  select 
    @value = Dict.value(
      '(StringDictionary/Pair[@Key=sql:variable("@key")])[1]', 
      'nvarchar(max)'
    ) 
    from TableWithXmlColumn
    where Id = @id
  return @value
end

我没有验证它,但这也可能执行得更好,因为它避免了T-SQL和XQuery引擎上的上下文切换,只需要一个XQuery。

我刚意识到,您没有指定一个ID的dict xml是否可能包含多个具有相同键的pair元素:

insert TableWithXmlColumn (Dict) values (
  N'<?xml version="1.0" encoding="utf-16"?>
    <StringDictionary>
      <Pair Key="color">red</Pair>
      <Pair Key="color">blue</Pair>
      <Pair Key="count">123</Pair>
    </StringDictionary>')

如果是这种情况,那么考虑这个稍微修改过的函数,它使用一个flwor xquery来枚举和组合多个值(如果存在的话)。不过,请注意,在这种情况下,只有在找不到@id时,函数才会返回空值,而在没有匹配的@key pair元素时不会返回空值。

create function DictValue(
  @id int,
  @key nvarchar(max)
) 
returns nvarchar(max) as 
begin
  declare @value nvarchar(max)
  select 
    @value = Cast(
      Dict.query('
        for $i in StringDictionary/Pair[@Key=sql:variable("@key")] 
        return string($i)
      ') as nvarchar(max)) 
    from TableWithXmlColumn
    where Id = @id
  return @value
end

祝你好运!

Mitchel Sellers 16 年前

就我个人而言,我看不出你能做什么,你所拥有的代码是以一种非常易读的方式构造的,你首先查询XML以获得结果集,然后获取值。

您也许可以绕过@d变量的使用,但是,我相信代码的可读性将受到很大的影响。

Joe 13 年前

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[XMLTable](@x XML)  
RETURNS TABLE 
AS RETURN 
WITH cte AS (  
SELECT 
        1 AS lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(NULL AS NVARCHAR(MAX)) AS ParentName, 
        CAST(1 AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,  
        x.value('local-name(.)','NVARCHAR(MAX)')  
        + N'[' 
        + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)  
        + N']' AS XPath,  
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position, 
        x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,  
        x.value('text()[1]','NVARCHAR(MAX)') AS Value,  
        x.query('.') AS this,         
        x.query('*') AS t,  
        CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,  
        CAST(1 AS INT) AS ID  
FROM @x.nodes('/*') a(x)  
UNION ALL 
SELECT 
        p.lvl + 1 AS lvl,  
        c.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(p.Name AS NVARCHAR(MAX)) AS ParentName, 
    CAST(p.Position AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath,  
        CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')+ N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') 
        ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,  
        ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
        ORDER BY (SELECT 1)) AS Position, 
        CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,  
        CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, c.query('.') AS this,  
        c.query('*') AS t,  
        CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,  
        CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)  
FROM cte p  
CROSS APPLY p.t.nodes('*') b(c)), cte2 AS (  
                                            SELECT 
                                            lvl AS Depth,  
                                            Name AS NodeName,  
                                            ParentName, 
                                            ParentPosition, 
                                            NodeType,  
                                            FullPath,  
                                            XPath,  
                                            Position, 
                                            Tree AS TreeView,  
                                            Value,  
                                            this AS XMLData,  
                                            Sort, ID  
                                            FROM cte  
UNION ALL 
SELECT 
        p.lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)'),  
        p.Name, 
        p.Position, 
        CAST(N'Attribute' AS NVARCHAR(20)),  
        p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        1, 
        SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)  
        + N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        x.value('.','NVARCHAR(MAX)'),  
        NULL,  
        p.Sort,  
        p.ID + 1  
FROM cte p  
CROSS APPLY this.nodes('/*/@*') a(x)  
)  
SELECT 
        ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,  
        ParentName, ParentPosition,Depth, NodeName, Position,   
        NodeType, FullPath, XPath, TreeView, Value, XMLData 
FROM cte2