Querying for distinct data in XML Column in SqlServer 2005

Question

I have some xml data stored in an XML Column in a table in sql server 2005.

Record1 would have data for that column would look like this:

<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"&gt;
  <Things>
    <Thing>
        <Name>Monkey</Name>
    </Thing>
    <Thing>
        <Name>Lion</Name>
    </Thing>
    <Thing>
        <Name>Shoe</Name>
    </Thing>
  </Things>
</ArrayOfThings>

Record 2 might have data like this for that column

<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"&gt;
  <Things>
    <Thing>
        <Name>Monkey</Name>
    </Thing>
    <Thing>
        <Name>Elephant</Name>
    </Thing>
    <Thing>
        <Name>Hammer</Name>
    </Thing>
    <Thing>
        <Name>Bucket</Name>
    </Thing>
  </Things>
</ArrayOfThings>

Can anyone help me with what the syntax would look like to select distinct things from this table.

The results returned would look like this: Monkey Lion Shoe Elephant Hammer Bucket

obviously this is not production data :)

Setup script:

CREATE TABLE [SomeSchema].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ThingData] [xml] NULL,
 CONSTRAINT [PK_Party] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
) ON [PRIMARY]
GO

INSERT INTO [SomeSchema].[MyTable]
           ([ThingData])
     VALUES
           ( 
'<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"&gt;
  <Things>
    <Thing>
        <Name>Monkey</Name>
    </Thing>
    <Thing>
        <Name>Lion</Name>
    </Thing>
    <Thing>
        <Name>Shoe</Name>
    </Thing>
  </Things>
</ArrayOfThings>
')
GO

INSERT INTO [SomeSchema].[MyTable]
           ([ThingData])
     VALUES
           (
'<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"&gt;
  <Things>
    <Thing>
        <Name>Monkey</Name>
    </Thing>
    <Thing>
        <Name>Elephant</Name>
    </Thing>
    <Thing>
        <Name>Hammer</Name>
    </Thing>
    <Thing>
        <Name>Bucket</Name>
    </Thing>
  </Things>
</ArrayOfThings>
')
GO

And the select would go against the column in the table

Answer 1

You could also easily use the SQL Server 2005 built-in XQuery language instead of the clunky of OPENXML stuff and achieve the same result very easily:

DECLARE @input XML 
SET @input = '<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"&gt;
  <Things>
    <Thing>
        <Name>Monkey</Name>
    </Thing>
    <Thing>
        <Name>Elephant</Name>
    </Thing>
    <Thing>
        <Name>Hammer</Name>
    </Thing>
    <Thing>
        <Name>Bucket</Name>
    </Thing>
  </Things>
</ArrayOfThings>'

;WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/SomeCompany' AS ns)
SELECT
    DISTINCT Array.Things.value('(ns:Name)[1]', 'varchar(50)')
FROM
    @input.nodes('/ns:ArrayOfThings/ns:Things/ns:Thing') AS Array(Things)

You basically create a "pseudo-table" called Array.Things that contains one "row" for each entry of that specified type - here a <Thing> inside the structure given.

Then you can reach into those "rows" and grab out the individual elements, here the <Name> value, and you can select those and work with them.

Answer 2

create table #t1(id int not null identity(1,1),ThingData xml)

insert #t1(ThingData) values ( '<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"&gt; <Things> <Thing> <Name>Monkey</Name> </Thing> <Thing> <Name>Lion</Name> </Thing> <Thing> <Name>Shoe</Name> </Thing> </Things></ArrayOfThings>')

insert #t1(ThingData) values ( '<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"&gt; <Things> <Thing> <Name>Monkey</Name> </Thing> <Thing> <Name>Elephant</Name> </Thing> <Thing> <Name>Hammer</Name> </Thing> <Thing> <Name>Bucket</Name> </Thing> </Things></ArrayOfThings>')

;WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/SomeCompany' AS ns) select DISTINCT Array.Things.value('(ns:Name)[1]', 'varchar(50)') from #t1 cross apply #t1.[ThingData].nodes('/ns:ArrayOfThings/ns:Things/ns:Thing') as Array(Things)

Answer 3

If to use

Setup script:
CREATE TABLE [SomeSchema].[MyTable]( ....

from question, then:

WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/SomeCompany' AS ns) 
select DISTINCT Array.Things.value('(ns:Name)[1]', 'varchar(50)') Name 
FROM    [SomeSchema].[MyTable] MT
CROSS APPLY 
MT.ThingData.nodes('/ns:ArrayOfThings/ns:Things/ns:Thing') 
AS Array(Things)