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">
  <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">
  <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">
  <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">
  <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

Do you want them in one line?

Gennady Vanin Геннадий Ванин
– Gennady Vanin Геннадий Ванин

2010-10-12 14:23:53 +00:00
Commented Oct 12, 2010 at 14:23 — Gennady Vanin Геннадий Ванин
– Gennady Vanin Геннадий Ванин, Commented Oct 12, 2010 at 14:23
No, just those values in the result set

adev
– adev

2010-10-12 21:17:24 +00:00
Commented Oct 12, 2010 at 21:17 — adev
– adev, Commented Oct 12, 2010 at 21:17

JoshRoss · Accepted Answer · 2010-10-12 22:28:22Z

1

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

edited Oct 12, 2010 at 22:28

answered Oct 12, 2010 at 22:13

JoshRoss

3352 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JoshRoss Over a year ago

I used a temporary table here, but I hope you get the idea. Good artists copy, great artists steal.

marc_s · Accepted Answer · 2010-10-12 04:59:20Z

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">
  <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.

answered Oct 12, 2010 at 4:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

marc_s Over a year ago

@vgv8: they're quite obviously NOT in the input XML as defined and shown by the OP (as record #2).... if they're not there, they can't be retrieved.... as simple as that.....

adev Over a year ago

I converted this to use the table in my example SELECT @input = thingdata from [System].[mytable]; and it only returns data from the first record and doesn't consider the data in following rows.

Gennady Vanin Геннадий Ванин · Accepted Answer · 2010-10-13 12:32:48Z

0

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)

edited Oct 13, 2010 at 12:32

answered Oct 13, 2010 at 6:45

Gennady Vanin Геннадий Ванин

10.5k12 gold badges81 silver badges111 bronze badges

Collectives™ on Stack Overflow

Querying for distinct data in XML Column in SqlServer 2005

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related