querying xml - 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)

querying xml

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2014-05-08 : 15:44:45

Hey folks

Given the below xml how can I filter by xml attribute k='keyword' or k='count'.

</results>'
<result offset=''99''>
<field k=''keyword''>
<value><text>yoga</text></value>
</field>
<field k=''count''>
<value><text>1</text></value>
</field>
<field k=''percent''>
<value><text>0.138313</text></value>
</field>
</result>
</results>'

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2014-05-08 : 15:47:37

ok i think I got it

select T.c.value('@k', 'varchar(100)'), T.c.value('value[1]', 'varchar(100)')
from @myDoc.nodes('/results/result/field') T(c)
where T.c.value('@k', 'varchar(100)') = 'keyword'

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2014-05-08 : 17:06:16

hola,

select T.c.value('@k', 'varchar(100)') as ValueType, T.c.value('value[1]', 'varchar(100)') value, T.c.value('@offset', 'varchar(100)')
from @myDoc.nodes('/results/result/field') T(c)

ok how do I get the value of offset in there?

Gracias

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2014-05-08 : 17:08:12

:( i got it

select T.c.value('@k', 'varchar(100)') as ValueType, T.c.value('value[1]', 'varchar(100)') value, T.c.value('../@offset', 'varchar(100)')
from @myDoc.nodes('/results/result/field') T(c)

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-05-08 : 17:09:19

[code]DECLARE @Data XML = '
<results>
<result offset="99">
<field k="keyword">
<value>
<text>yoga</text>
</value>
</field>
<field k="count">
<value>
<text>1</text>
</value>
</field>
<field k="percent">
<value>
<text>0.138313</text>
</value>
</field>
</result>
</results>';

SELECT t.c.value('@k', 'varchar(100)') AS [Attribute @k],
x.c.value('.', 'varchar(100)') AS [Elementtext text]
FROM @Data.nodes('/results/result/field[@k="keyword" or @k="count"]') AS t(c)
OUTER APPLY t.c.nodes('value/text') AS x(c)[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2014-05-09 : 12:44:08

thanks Peso!

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources