Querying XML Data Examples

queryingXMLDataWithSQL.sql

/*******************************************************
    test data to work with 
*******************************************************/
if object_id('tempdb..#sample') is not null
    drop table #sample;
select
    *
into #sample
from
    (
    values
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml)),
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml)),
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml)),
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml)),
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml)),
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml)),
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml)),
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml)),
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml)),
    ('#sample', cast('<customer><account customertype="1" regionid="9002" /><account customertype="1" regionid="5014" /><account customertype="1" regionid="1008" /><account customertype="1" regionid="5005" /></customer>' as xml))) as vtable
    ([query_description], [fancyxml])


/*******************************************************
msdn documentation
https://goo.gl/njfjnu
    
*******************************************************/

select
top (10)
    query_description = 'raw data as example'
   ,fancyxml
from
    #sample as s



select top 5
    query_description =               'parsed xml'
   ,fancyxml
   ,fancyxmlvalue = fancyxml.value('(/customer/account/@customertype)[1]', 'int')
   ,fancyxmlvalue = fancyxml.value('(/customer/account/@regionid)[1]', 'int')
from
    #sample as s

/*******************************************************
performance note from msdn
    for performance reasons, instead of using the value() method 
    in a predicate to compare with a relational value, use exist() 
    with sql:column(). this is shown in example d that follows.


*******************************************************/
declare @regionid int = 9002;
select top 5
    query_description = 'parsed xml with exists validating data'
   ,fancyxml
   ,fancyxmlvalue =     fancyxml.value('(/customer/account/@customertype)[1]', 'int')
   ,fancyxmlvalue =     fancyxml.value('(/customer/account/@regionid)[1]', 'int')
from
    #sample as s
where
    fancyxml.exist('/customer/account[@regionid=9002]') = 1; -- exists returns a boolean value


/*******************************************************
    other examples
*******************************************************/

select
    query_description = 'get a child node'
   ,fancyxml.query('//customer/account')
from
    #sample


select
    query_description = 'query function, matching exact parameter'
   ,fancyxml.query('//customer/account[@regionid=9002]')
from
    #sample


select
    query_description = 'query function, matching exact parameter'
   ,fancyxml.query('//customer/account[@regionid=9002]')
from
    #sample



/*******************************************************
    turning the xml column into a multiple result set normalized for db/tempdb
    stack overflow resource: http://goo.gl/7dkbaf

-- example
    declare @r table
        (
        aliasesvalue xml
        )
insert into @r
select
    '<aliases>   <alias>     <aliastype>aka</aliastype>     <aliasname>pramod singh</aliasname>   </alias>   <alias>     <aliastype>aka</aliastype>     <aliasname>bijoy bora</aliasname>   </alias> </aliases> '


select
    c.query('data(aliastype)')
   ,c.query('data(aliasname)')
from
    @r r
    cross apply aliasesvalue.nodes('aliases/alias') x (c)
*******************************************************/

/*******************************************************
    important xpath query is case sensitive!
*******************************************************/

select
    query_description = 'example nodes being cross applied to create a normalized table structure'
   ,s.fancyxml
   ,c.query('data(@customertype)')
   ,c.query('data(@regionid)')
from
    (
        select top (1)
            *
        from
            #sample as s
    ) as s

    cross apply fancyxml.nodes('//customer/account') x (c)


select
    query_description = 'expanded example of all the results'
   ,s.fancyxml
   ,c.query('data(@customertype)')
   ,c.query('data(@regionid)')
from
    #sample as s

    cross apply fancyxml.nodes('//customer/account') x (c)