This is a follow up on a prior post I've made a few months back on XQuery, with the purpose of this post to just show a few more examples on how powerful XML and XQuery can be -- with no additional overhead costs.  If implemented correctly, you can even get better performance gains; but I'll possibly discuss that in a future offering. For this segment, I wanted to document the common usages I've come across lately, and refer to it later on as a refresher.

Example 1:

So, you've got xml data, and the structure looks as such:

    
        key1
        value1
    
    
        key2
        value2
    

To query this data, we do something like this:

declare @xml xml
set @xml = N'

key1value1
key2value2
key3value3
'
select 
metadata.[item].value('key[1]', 'nvarchar(25)') as [key],
metadata.[item].value('value[1]', 'nvarchar(max)') as [value],
metadata.[item].value('value[1]', 'nvarchar(25)') as [valueShort]
from @xml.nodes('/metadata/item') metadata ([item])

Results:

key                       value                     valueShort
------------------------- ------------------------- -------------------------
key1                      value1                    value1
key2                      value2                    value2
key3                      value3                    value3

(3 row(s) affected)

Example 2:

If you're an attribute guy (or gal), here's one that'll work too:

declare @xml xml
set @xml = N'





'

select metadata.[item].query('data(@key)').value('.', 'nvarchar(25)') as [key]
,metadata.[item].query('data(@value)').value('.', 'nvarchar(max)') as [value]
,metadata.[item].query('data(@value)').value('.', 'nvarchar(25)') as [valueshort]
from @xml.nodes('/metadata/item') metadata ([item])

Results:

key                       value                     valueshort
------------------------- ------------------------- -------------------------
key1                      value1                    value1
key2                      value2                    value2
key3                      value3                    value3

(3 row(s) affected)

Happy coding!

-Kennydust