Sql Server XQuery value() not working

Question

I have been working for some time now on XQUERY but somehow I am stuck at this simple yet weird problem.

Following is my query :

    DECLARE @VALUE_XML XML ='<ns2:Transaction xmlns:ns2="broadridge:spsi:gloss:Transaction" xmlns="broadridge:spsi:gloss:Common" xmlns:ns3="broadridge:spsi:gloss:FinancingTransactionEvent">
      <ns2:Date>
        <Type>TDAT</Type>
        <ns2:Date>2015-02-06</ns2:Date>
      </ns2:Date>
      <ns2:Date>
        <Type>VDAT</Type>
        <ns2:Date>2015-02-06</ns2:Date>
      </ns2:Date>
      <ns2:Driver>
        <Type>CACT</Type>
        <ns2:Driver>XXXX</ns2:Driver>
      </ns2:Driver>
      <ns2:Driver>
        <Type>SUBT</Type>
        <ns2:Driver>BKST</ns2:Driver>
      </ns2:Driver>
      <ns2:Driver>
        <Type>OPER</Type>
        <ns2:Driver>DEL</ns2:Driver>
      </ns2:Driver>
      <ns2:Reference>
        <ns2:Type>CCAR</ns2:Type>
        <ns2:Reference>2015-08-12T07:39:26Z</ns2:Reference>
      </ns2:Reference>
      <ns2:Price>
        <Type>TPRC</Type>
        <ns2:Price>0.000000</ns2:Price>
        <ns2:MultiplyDivide>M</ns2:MultiplyDivide>
      </ns2:Price>
      <ns2:Party>
        <Type>COMP</Type>
        <ns2:Reference>
          <Type>AEID</Type>
          <Value>ING7</Value>
        </ns2:Reference>
      </ns2:Party>
      <ns2:Party>
        <Type>PBK</Type>
        <ns2:Reference>
          <Type>ABID</Type>
          <Value>INGBK900611</Value>
        </ns2:Reference>
      </ns2:Party>
      <ns2:Party>
        <Type>SECP</Type>
        <ns2:Reference>
          <Type>ACID</Type>
          <Value>2009453</Value>
        </ns2:Reference>
      </ns2:Party>
      <ns2:Instrument>
        <Type>UINS</Type>
        <ns2:Reference>
          <Type>APTP</Type>
          <Value>722232</Value>
        </ns2:Reference>
        <ns2:Quantity>37043</ns2:Quantity>
      </ns2:Instrument>
      <ns2:Instrument>
        <Type>GREI</Type>
        <ns2:Reference>
          <Type>ISO</Type>
          <Value>EUR</Value>
        </ns2:Reference>
        <ns2:Quantity>0.00</ns2:Quantity>
      </ns2:Instrument>
      <ns2:Instrument>
        <Type>NWHI</Type>
        <ns2:Reference>
          <Type>ISO</Type>
          <Value>EUR</Value>
        </ns2:Reference>
        <ns2:Quantity>0.00</ns2:Quantity>
      </ns2:Instrument>
      <ns2:OriginReference>SN287823109_1</ns2:OriginReference>
      <ns2:OriginVersion>3075532</ns2:OriginVersion>
    <ns2:Action>N</ns2:Action>
      <ns2:Type>BCAS</ns2:Type>
      <ns2:Origin>SOPHIS</ns2:Origin>
    </ns2:Transaction>'

select @VALUE_XML.value('declare default element namespace  "broadridge:spsi:gloss:Transaction";
(/Transaction/Date/Type)[1]', 'nvarchar(max)')

I really can't decipher why this query is returning 'NULL'. I am expecting value of first 'Type' element i.e. 'TDAT'.

I am really Banging my head. i have used value() n number of times but can't understand why it's not working today.

regards, Vikas

Answer 1

Your XML default namespace URI is "broadridge:spsi:gloss:Common" :

select @VALUE_XML.value('
    declare default element namespace  "broadridge:spsi:gloss:Common";
    declare namespace ns2="broadridge:spsi:gloss:Transaction";
    (/ns2:Transaction/ns2:Date/Type)[1]
', 'nvarchar(max)')