xquery data stored by rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

xquery data stored by rows

Author

Topic

Mikeyboy01
Starting Member

4 Posts

Posted - 2011-05-02 : 14:03:46

I am trying to xquery data stored as an xml datatype in the following format. Using SQLServer 2008, I'd like to be able to pull the value from var5 where var4 = "Field4".

TIA,
Mike

<XmlData>
<Result>
<row var1="1" var2="1" var3="1" var4="Field1" var5="Value1" />
<row var1="1" var2="1" var3="1" var4="Field2" var5="Value2" />
<row var1="1" var2="1" var3="1" var4="Field3" var5="Value3" />
<row var1="1" var2="1" var3="1" var4="Field4" var5="Value4" />
<row var1="1" var2="1" var3="1" var4="Field5" var5="Value5" />
<row var1="1" var2="1" var3="1" var4="Field6" var5="Value6" />
</Result>
</XmlData>

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-02 : 14:52:31

[code]select @yourXMLVariable.query('data(//row[@var4="Field4"]/@var5)[1]');[/code]

Mikeyboy01
Starting Member

4 Posts

Posted - 2011-05-02 : 15:23:39

Sunita thanks for your reply. Not sure what I should replace @yourXMLVariable with. let's say the above data is stored as an xml datatype in a column name xmldata in a table called Data. I tried

select xmldata.query('data(//row[var4="Field4"]/var5)[1]')
from Data

and returned no results.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-02 : 16:01:47

You need the "@" signs. The @sign is indicating that you are looking for an attribute. Alternatively, you could use the keyword attribute::. So either of the following should work.


select 
  xmldata.query('data(//row[@var4="Field4"]/@var5)[1]')
  -- or this
  -- ,
  --xmldata.query('data(//row[attribute::var4="Field4"]/attribute::var5)[1]')
from Data

The query is really saying the following
1. Find me the element named "row" anywhere in the tree.
2. Filter it by attribute var4 and pick only those that have var4 = Field4.
3. From those that you filtered, give me the attribute @var5.

Mikeyboy01
Starting Member

4 Posts

Posted - 2011-05-02 : 16:39:08

That did it. Thank you so much for the help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-02 : 17:50:52

You are quite welcome!

I sort of implicitly assumed that there would be only one element in any given row in your table that has element name = row and attribute var4 = Field4. If that is not the case, for example, if your data was like what I am showing below, you will need to modify the query - but if that is not an issue, you are good.


<XmlData>
<Result>
<row var1="1" var2="1" var3="1" var4="Field4" var5="Value4" />
<row var1="1" var2="1" var3="1" var4="Field4" var5="secondValue4" />
</Result>
</XmlData>

or even this:


<XmlData>
<Result>
<row var1="1" var2="1" var3="1" var4="Field4" var5="Value4" />
</Result>
</XmlData>
<XmlData>
<Result>
<row var1="1" var2="1" var3="1" var4="Field4" var5="Value4" />
</Result>
</XmlData>

The second kind is possible in SQL XML because it does not require that you have only one root element, i.e., XML fragments are acceptable to SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources