SQL Server XML Query Advice

Question

I'm writing a user-defined function to extract values from an XML column in SQL Server which represents a simple dictionary of string key-value pairs. The only way I've made it work so far seems overly complex. Do you have any simplifying suggestions or tips for the DictValue function below?

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]

Answer 1

Personally I don't see much that you can do, the code that you have is structured in a way that is very readable, and you are querying the XML to get the result set first, then grabbing the values.

You might be able to get around the use of the @d variable, however, I believe the readability of the code will suffer greatly.

Answer 2

Second the answer by Mitchel - looks pretty much down the bare minimum - no fluff or superfluous code that can be reduced, I'd say.

The only question would be: why do you store this in XML, and not in a relational table? The key/value pair seems fairly structured and ideally suited for relational storage...

Marc

Answer 3

I find the following variable-bound XQuery approach easier to understand:

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

I didn't verify it, but this might perform a bit better, too, as it avoids context switching on the T-SQL and XQuery engines and requires only one XQuery.

I just realized that you didn't specify whether the Dict XML for one Id might contain multiple Pair elements with the same Key:

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>')

If that's the case then consider this slightly modified function, which uses a FLWOR XQuery to enumerate and combine multiple values, if they exist. Note, though, that in this case the function will only return NULL when an @id is not found, not when there's no matching @key Pair element.

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

Good luck!