CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery-sql
TonyP
TonyP

Reputation: 5873

How to retrieve attribute only using xQuery

I have a function similiar to the following, returning large chunk of xml (in production environment), In various stages of the application , we need to retrieve only some of the elements or only some attributes from this. 

create function testrig.fxConfigurations(@context varchar(300)) returns xml as 
begin
return (select 
'<configurations realm="configuration">
    <context name="apera">
        <backends>
            <backend name="Hades">
                <os>HP Unix</os>
                <ip>nnn.nnn.nnn</ip>
                <db vender="Oracle" version="11g">
                    <netconnect>Data Source= (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = hades)(PORT = 1521)(RECV_BUF_SIZE=1048576))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = baan)));Password=********;User ID=ItsMe;" providerName="Oracle.DataAccess.Client"
                    </netconnect>
                </db>
            </backend>
            <backend name="Athena">
                <os>HP Unix</os>
                <ip>nnn.nnn.nnn</ip>
                <db vender="Oracle" version="11g">
                    <netconnect>Data Source= (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = hades)(PORT = 1521)(RECV_BUF_SIZE=1048576))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = baan)));Password=********;User ID=ItsMe;" providerName="Oracle.DataAccess.Client"
                    </netconnect>
                </db>
            </backend>
        </backends>
    </context>
</configurations>
')

end 
go

How do I retrieve only the attribute name i.e 'Hades','Athena' using xQuery like this

select (testrig.fxConfigurations(null).query('configurations/context[@name="apera"]/backends').query('/descendant-or-self::*'))

Upvotes: 2

Views: 155

Answers (1)

StuartLC
StuartLC

Reputation: 107387

If you want to concatenate all values of the backend/@name attributes (ala GROUP_CONCAT):

select testrig.fxConfigurations(null)
   .query('data(configurations/context[@name="apera"]/backends/backend/@name)')

If you need to work with the attributes individually, you may need to use nodes to extract them:

WITH cteXml AS
(
    SELECT testrig.fxConfigurations(null)
           .query('configurations/context[@name="apera"]') as context
)
SELECT Nodes.node.value('@name', 'varchar(50)') AS backEndName
FROM cteXml
    CROSS APPLY cteXml.context.nodes('//backends/backend') as Nodes(node);

Fiddle here

Upvotes: 1

Related Questions