'Get data from XML column

I have a table with a column of datatype XML. I want to get data by reading that XML column.

Here is the XML which is stored in that column:

<BizMsg xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:asx:xsd:xasx.802.001.04 ASX_AU_CHS_comm_802_001_04_xasx_802_001_04.xsd">
  <Document xmlns="urn:iso:std:iso:20022:tech:xsd:sese.023.001.07">
    <SctiesSttlmTxInstr>
      <TxId>
        01114|0045852600
      </TxId>
    </SctiesSttlmTxInstr>
  </Document>
</BizMsg>

I want to get value inside the <TxId> tag.

I tried to run this query but I am not getting any result:

DECLARE @myDoc XML  
SET @myDoc = ( Select data from TableName Where Id = 56 )   // which returns XML column value from table
  
SELECT  @myDoc.value('(/BizMsg/Document/SctiesSttlmTxInstr/TxId)[1]', 'nvarchar(max)' )

Please advise - what am I doing wrong?

sql-serverxml


Solution 1:[1]

You're ignoring the defined XML namespaces in your document - that's why you're not getting any results....

<BizMsg xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
        xmlns="urn:asx:xsd:xasx.802.001.04 ASX_AU_CHS_comm_802_001_04_xasx_802_001_04.xsd">
        **** this is the root level XML namespace
    <Document xmlns="urn:iso:std:iso:20022:tech:xsd:sese.023.001.07">
              **** this is the XML namespace for <Document> and its children

Try this:

WITH XMLNAMESPACES ('urn:asx:xsd:xasx.802.001.04 ASX_AU_CHS_comm_802_001_04_xasx_802_001_04.xsd' AS RootNS,
                    'urn:iso:std:iso:20022:tech:xsd:sese.023.001.07' AS DocNS)
SELECT
    TxId = XC.value('(DocNS:SctiesSttlmTxInstr/DocNS:TxId/text())[1]', 'varchar(100)')
FROM
    YourTableNameHere
CROSS APPLY
    XmlDoc.nodes('/RootNS:BizMsg/DocNS:Document') AS XT(XC)

With your input, I get this result:

TxId
----------------
01114|0045852600

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 marc_s

Related Questions