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)

Maxim Fazyloff · Accepted Answer · 2019-07-31 12:06:03Z

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

Collectives™ on Stack Overflow

T-sql select xml value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related