T-sql select xml value

Question

<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">  
<soap:Header>   
    <Teste xmlns="http://google.com">    
        <Username></Username>    
        <Password></Password>    
        <Firma>Blabla</Firma>   
    </Teste>  
</soap:Header>  
<soap:Body>   
    -- Omitted
</soap:Body>
</soap:Envelope>

Having this xml in a column, how to retrieve the Firma value ? What's giving me more trouble is the "soap" namespaces that seem to be ruining my query's

This is what I was experimenting with, but not working as expected since the path is wrong

select ref.col.value('.','varchar(255)')
from my_View tbl
cross apply tbl.XMLColumn.nodes('Envelope/Header/Teste/Firma') as ref(col)

score 2 · Accepted Answer · answered Jul 31 '19 at 12:06

You must use namespaces in your Xquery

   create table so_Test (XMLColumn xml)
    go
    insert so_Test
    select '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">  
    <soap:Header>   
        <Teste xmlns="http://google.com">    
            <Username></Username>    
            <Password></Password>    
            <Firma>Blabla</Firma>   
        </Teste>  
    </soap:Header>  
    <soap:Body>       
    </soap:Body>
    </soap:Envelope>'

    select XMLColumn.value('
    declare namespace soap="http://schemas.xmlsoap.org/soap/envelope/";
    declare namespace google="http://google.com";
    /soap:Envelope[1]/soap:Header[1]/google:Teste[1]/google:Firma[1]', 'varchar(255)') as Firma from  so_test

Result:

Firma

Blabla

T-sql select xml value

1 Answers1