Fetch a value from xml in SQL Stored procedure

Question

I tried to fetch an element from an XML in stored procedure as follows

     USE [ION]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE  [dbo].[AddToCustomer]
        (@id VARCHAR(20))
    AS
    BEGIN
        DECLARE @xx xml
        DECLARE @custname varchar(8000)
        DECLARE @fid varchar(20)
        SET NOCOUNT ON;

        SELECT @fid=[C_ID], @xx=cast([C_XML] as xml) FROM [ION].[dbo].[COR_INBOX_ENTRY] WHERE [C_WAS_PROCESSED]=0 and [C_ID]=@id

        if(@@ROWCOUNT!=0)
        BEGIN

            SELECT @xx.value('(/SyncMESCustomer/ApplicationArea/Sender/ConfirmationCode)[1]', 'varchar(100)')
        END

    END

I am pretty sure that the variable @xx is loaded with the xml. I need to select the value of '/SyncMESCustomer/ApplicationArea/Sender/ConfirmationCode' but its returning a NULL.

Sample xml is as follows

<SyncMESCustomer xmlns="http://schema.infor.com/InforOAGIS/2" xmlns:xs="http://www.w3.org/2001/XMLSchema" languageCode="GB" releaseID="9.2" systemEnvironmentCode="Production" versionID="2.9.1">
    <ApplicationArea>
        <Sender>
            <LogicalID schemeVersionID="15.4.0.0">lid://infor.m3be.ipc_mec_dev</LogicalID>
            <ComponentID schemeVersionID="MCP2-1514-02">M3BE</ComponentID>
            <ConfirmationCode>OnError</ConfirmationCode>
        </Sender>
        <CreationDateTime>2017-06-09T13:41:36.996Z</CreationDateTime>
        <BODID>5cfa6949-11ef-41dc-a380-4cc79f2687cc</BODID>
    </ApplicationArea>
    <DataArea>
        <Sync>
            <TenantID>TEST</TenantID>
            <AccountingEntityID>1_001</AccountingEntityID>
            <ActionCriteria>
                <ActionExpression actionCode="Replace" />
            </ActionCriteria>
        </Sync>
        <MESCustomer>
            <CUSTOMER_NO>TEST6</CUSTOMER_NO>
            <ipc_CSS_CUST_CUST_NAME>
                <CUSTOMER_NO>TEST6</CUSTOMER_NO>
                <CUST_NAME>Hawaii Inc_MES</CUST_NAME>
                <STATUS_DATE>20170609</STATUS_DATE>
                <STATUS_WORD>20</STATUS_WORD>
            </ipc_CSS_CUST_CUST_NAME>
            <STATUS_WORD>20</STATUS_WORD>
        </MESCustomer>
    </DataArea>
</SyncMESCustomer>

Thanks in advance.

Answer 1

This works for me... you can add another alias to your namespace...

 <SyncMESCustomer xmlns:xz="http://schema.infor.com/InforOAGIS/2" xmlns:xs="http://www.w3.org/2001/XMLSchema" languageCode="GB" releaseID="9.2" systemEnvironmentCode="Production" versionID="2.9.1"

DECLARE @xx XML

SET @xx = '<SyncMESCustomer>
    <ApplicationArea>
        <Sender>
            <LogicalID schemeVersionID="15.4.0.0">lid://infor.m3be.ipc_mec_dev</LogicalID>
            <ComponentID schemeVersionID="MCP2-1514-02">M3BE</ComponentID>
            <ConfirmationCode>OnError</ConfirmationCode>
        </Sender>
        <CreationDateTime>2017-06-09T13:41:36.996Z</CreationDateTime>
        <BODID>5cfa6949-11ef-41dc-a380-4cc79f2687cc</BODID>
    </ApplicationArea>
    <DataArea>
        <Sync>
            <TenantID>TEST</TenantID>
            <AccountingEntityID>1_001</AccountingEntityID>
            <ActionCriteria>
                <ActionExpression actionCode="Replace" />
            </ActionCriteria>
        </Sync>
        <MESCustomer>
            <CUSTOMER_NO>TEST6</CUSTOMER_NO>
            <ipc_CSS_CUST_CUST_NAME>
                <CUSTOMER_NO>TEST6</CUSTOMER_NO>
                <CUST_NAME>Hawaii Inc_MES</CUST_NAME>
                <STATUS_DATE>20170609</STATUS_DATE>
                <STATUS_WORD>20</STATUS_WORD>
            </ipc_CSS_CUST_CUST_NAME>
            <STATUS_WORD>20</STATUS_WORD>
        </MESCustomer>
    </DataArea>
</SyncMESCustomer>'

SELECT 
    xmlData.A.value('./ConfirmationCode[1]', 'varchar(100)')
FROM @xx.nodes('/SyncMESCustomer/ApplicationArea/Sender') xmlData(A);

Answer 2

;WITH XMLNAMESPACES(DEFAULT 'http://schema.infor.com/InforOAGIS/2','http://www.w3.org/2001/XMLSchema' as ns0)
Select @XML.value('SyncMESCustomer[1]/ApplicationArea[1]/Sender[1]/ConfirmationCode[1]','varchar(100)')

Returns

OnError