Query Xml in Sql Table for value given node attribute

Folks,

Lets say I have a table with 2 columns, one of which is xml.

Name   XmlData
-------   ---------
bob      <root><Column name="haircolor">blue</Column></root>
jane     <root><Column name="haircolor">green</Column><Column name="age">16</Column></root>
...

Is there a way to return the "haircolor" for a particular person?  More generally, how can I get the value of element Column where name attribute is a particular value?  is there a Select statement for this?  I'm using Sql Server 2005
LVL 1
sfun28Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paulwayCommented:
Sure, you want to use LIKE.  So for example:

SELECT * FROM tblName WHERE XMLData LIKE '<root><Column name="haircolor">blue%'
or you could always do
WHERE (XMLData LIKE '%haircolor%' AND XMLData LIKE '%blue%')
0
Anthony PerkinsCommented:
Or you could do it right and use the Xml Data methods. :)
0
sfun28Author Commented:
I think there's some confusion here.  I'm looking for the query to return blue or green, I don't know in advance what that value is.  So I want bob's haircolor, or jane's haircolor, i'm not looking for who has blue hair.  makes sense?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Anthony PerkinsCommented:
Something like this:
SELECT [Name], [XmlData].value('(root/Column/@name)[1]', 'varchar(20)')
FROM YourTable
WHERE [Name] = 'Bob'

Open in new window

0
sfun28Author Commented:
acperkins - how would I use that query for haircolor?  I see @name which is the attribute.  could you provide the exact query?  The reason I'm asking is cause I can't figure out the syntax
0
Anthony PerkinsCommented:
Here is a complete sample:
DECLARE @MyTable TABLE ([Name] varchar(50), [Xmldata] xml)

INSERT	@MyTable([Name], [XmlData])
VALUES	('bob', '<root><Column name="haircolor">blue</Column></root>'),
	('jane', '<root><Column name="haircolor">green</Column><Column name="age">16</Column></root>')

SELECT	[Name], 
	[XmlData].value('(root/Column/@name)[1]', 'varchar(20)') HairColor
FROM	@MyTable
WHERE	[Name] = 'Bob'

Here is the output:
Name	HairColor
bob	haircolor

Open in new window

0
sfun28Author Commented:
I get a syntax error when I run the code.  Btw, haircolor isn't guaranteed to be the first Column.  I'm using Xml because the data isn't tabular.  so ...@name)[1] won't work.  I'm not looking to get the value of the first Column element.  I'm looking to get the value of the Column element who's name is "haircolor"
0
sfun28Author Commented:
figured it out:
SELECT  Name, [XmlData].value('/Column[@name=''haircolor''][1]','varchar(100)') HairColor
FROM    MyTable
0
Anthony PerkinsCommented:
>>Btw, haircolor isn't guaranteed to be the first Column.<<
There is no concept in Xml of first column.  Attributes can be in any place.  As you have discovered the [1] is just a way of assuring that you are getting a singleton.  That is all.

>>I'm looking to get the value of the Column element who's name is "haircolor"<<
No.  You are looking for the attribute whose name is "haircolor".  There are no columns in Xml, only elements and attributes.

>>I'm not looking to get the value of the first Column element. <<
I understood.  See my first comment.

>>figured it out:<<
Excellent!  Now please close the question.
0
Anthony PerkinsCommented:
Incidentally based on the Xml you posted, your XPath should be:
root/Column[@name=''haircolor''][1]
0
Anthony PerkinsCommented:
>>I get a syntax error when I run the code. <<
You are not using SQL Server 2008 or you have your Compatibility Level set to 90.
0
sfun28Author Commented:
>> There is no concept in Xml of first column.  Attributes can be in any place.  As you have discovered the [1] is just a way of assuring that you are getting a singleton.  That is all.
 I think you're confusing the notion of a tabular column with my Element Column in the Xml.  Perhaps I should have used a better element name.  I'm talking about the Element "Column".  In Xml there is a concept of order of Elements.

>> No.  You are looking for the attribute whose name is "haircolor".  There are no columns in Xml, only elements and attributes.
No, I'm not looking for an attribute.  I'm looking for an Element's value, where a particular attribute's value is a given value.  I'm looking for the value of Column (the Element that is) where the attribute "name" = "haircolor".

>> Incidentally based on the Xml you posted, your XPath should be:
root/Column[@name=''haircolor''][1]
Correct.  I'm missing "root"

>>I get a syntax error when I run the code. <<
Yes.  As I stated in my original question I'm using Sql Server 2005 =)
0
Anthony PerkinsCommented:
>>In Xml there is a concept of order of Elements.<<
When searching for an attribute or an element (which is exactly what you are doing) there is no difference whatsover.

>>No, I'm not looking for an attribute.  <<
You really need to get your facts straight. The following searches for the attribute called "name" with a value of "haircolor":
'root/Column[@name=''haircolor'']

>>Yes.  As I stated in my original question I'm using Sql Server 2005<<
Then why pray tell did you include the SQL Server 2008 zone?

Thank you for the points.
0
sfun28Author Commented:
There's misinformation in your post.

>> When searching for an attribute or an element (which is exactly what you are doing) there is no difference whatsover.
YES there is a difference and I just proved it out in SQL.  Take this for example:
<root><Column name="haircolor">blue</Column><Column name="haircolor">green</Column></root>
(in this example, bob has two haircolors).  
this will return blue: root/Column[@name=''haircolor''][1]
this will return green: root/Column[@name=''haircolor''][2]
ORDER MATTERS.  I just read part of APress's SQL Server 2008 & XML book (there's no book specific to 2005) and the purpose of the [1] is to ensure that a specific node is returned in the case that multiple nodes result from the path.
Anyways, in Xml its illegal to have two attributes with the same (case sensitive) name, so the notion of ordering within attributes for a particular attribute doesn't even make sense.

>> You really need to get your facts straight. The following searches for the attribute called "name" with a value of "haircolor": 'root/Column[@name=''haircolor'']
We're splitting hairs here.  Yes it search for name=haircolor, but it returns an Element, not an attribute.

>> Then why pray tell did you include the SQL Server 2008 zone?
Because the bulk of Xml features were introduced in Sql Server 2005.  Sql 2008 adds relatively few features for xml.  EE suggests posting to multiple zones and that was the most appropriate zone (I knew I wasn't asking for anything more advanced than what was introduced in 2005"

>> Thank you for the points
Thank you for the useful information that solved my problem.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>>Anyways, in Xml its illegal to have two attributes with the same (case sensitive) name, so the notion of ordering within attributes for a particular attribute doesn't even make sense.<<
Thank you for making my point.  Very succintly I may say.

>>We're splitting hairs here. <<
Again, I agree with you.  You are.

>>Sql 2008 adds relatively few features for xml. <<
I did not say it had to do with Xml.  I was merely pointing out that the syntax error you received was because you were not using SQL Server 2008.

>>Thank you for the useful information that solved my problem.<<
So are you saying you do not need help with your other question (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25979396.html)?  Incidentlally, there is no need to use Dynamic SQL for such a simple task.  It is one of the prime causes of SQL Injection.
0
sfun28Author Commented:
>> So are you saying you do not need help with your other question (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25979396.html)?  

No. When did I say that?  You sarcastically thanked me for assigning points.  What's the value of the EE community if points are assigned where the solution wasn't provided?  I'm happy to assign partial points to an Expert that provides information that leads to a solution.  In this case I honestly felt like you didn't read my question.  This is a frustration I have with EE and it has been getting worse over the years.  Experts are quick to respond because they want points, but they fail to read and grasp the question and it creates hours of back-and-forth communication.  I couldn't have been more clear with my question "More generally, how can I get the value of element Column where name attribute is a particular value? "  I'm specifically asking for the VALUE of an ELEMENT.  "where name attribute is a particular value" clearly implies a search-and-match for name="value".  That was my ask.  Your solution dated 04/20/10 02:38 PM doesn't search for name="haircolor".  And now we're off on a multi-response back-and-forth that honestly feels like an ego-match.  I'm happy to admit when I'm wrong and I've been wrong plenty of times on EE.  In this case, I firmly believe that another member of our community who is facing the same issue as I am would derive the most value out of my comments because they drive towards a solution to THIS particular problem. I don't think any less of you or your abilities, and clearly you've added lots of value on other questions given your Genius status and contribution to the community, for which I thank you.  I'll leave it there.

>> Incidentlally, there is no need to use Dynamic SQL for such a simple task.  It is one of the prime causes of SQL Injection
I'd love to see this solution!  Shall I post the same question but specify "without dynamic sql"?  I try to avoid dynamic sql where possible.
0
Anthony PerkinsCommented:
Fair enough, here you go:
DECLARE @MyTable TABLE ([Name] varchar(50), [Xmldata] xml) 
 
INSERT  @MyTable([Name], [XmlData]) 
VALUES  ('bob', '<root><Column name="haircolor">blue</Column></root>')

INSERT  @MyTable([Name], [XmlData]) 
VALUES  ('jane', '<root><Column name="haircolor">green</Column><Column name="age">16</Column></root>') 

SELECT	*
FROM	@MyTable 

SELECT  [Name],  
        [XmlData].value('(root/Column[@name="haircolor"])[1]','varchar(20)') HairColor,
        [XmlData].value('(root/Column[@name="age"])[1]','tinyint') Age
FROM    @MyTable 

Open in new window

0
Anthony PerkinsCommented:
Let's try that again:
DECLARE @MyTable TABLE ([Name] varchar(50), [Xmldata] xml) 
 
INSERT  @MyTable([Name], [XmlData]) 
VALUES  ('bob', '<root><Column name="haircolor">blue</Column></root>')

INSERT  @MyTable([Name], [XmlData]) 
VALUES  ('jane', '<root><Column name="haircolor">green</Column><Column name="age">16</Column></root>') 

SELECT  [Name],  
        [XmlData].value('(root/Column[@name="haircolor"])[1]','varchar(20)') HairColor,
        [XmlData].value('(root/Column[@name="age"])[1]','tinyint') Age
FROM    @MyTable 

Output:
Name	HairColor	Age
bob	blue	NULL
jane	green	16

Open in new window

0
sfun28Author Commented:
hi acperkins - that works in the case that I know haircolor and age are present.  

My question states:  "Lets also say that a priori I don't know the actual values of the "name" attribute.  Here I show "haircolor" and "title" but it could be others."

I see why the Expert used Dynamic Sql now.  I don't think there's a way around that given the restriction I'm placing on knowing which name attribute values are present.
0
Anthony PerkinsCommented:
Unless you know the attribute names no amount of Dynamic SQL is going to help you.
0
Anthony PerkinsCommented:
In other words with T-SQL you have to know what you are retrieving in order to get it. Using Dynamic SQL does not change that.
0
sfun28Author Commented:
but the proposed solution using Dynamic Sql works just fine =)
0
Anthony PerkinsCommented:
>>but the proposed solution using Dynamic Sql works just fine =)<<
You are missing the point.  It is no different than the solution I posted here without the risk of using Dynamic SQL.  It still requires knowing the names of the attributes.
0
sfun28Author Commented:
>> You are missing the point.  It is no different than the solution I posted here without the risk of using Dynamic SQL.  It still requires knowing the names of the attributes.

huh?  you first suggested that there's no need to use Dynamic Sql for my task.  I asked how that's possible.  You posted code that didn't solve the task (I posted a stipulation in the task from the original question that wasn't satisfied by your code).  You said that unless I know the attribute names no amount of Dynamic SQL is going to help.  But my point is that another expert HAS offered a Dynamic Sql solution that DOES help.  see the post.  teh solution uses Dynamic Sql and doesn't require me to know the attribute values a priori.
0
Anthony PerkinsCommented:
I give up.

Good luck.
0
sfun28Author Commented:
acperkins - This is a bit frustrating.  Here's a second example of not reading the question.  Honestly, how can any expert be useful to the community if they don't take the time to read and digest the question?  Honestly, your comments baffle me.  Its almost as if you just type without reading at all.
0
Ferruccio_GuicciardiCommented:
Thanks! very handy! :)
0
Anthony PerkinsCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.