XML from SQL Server-CodePudding

tried below, but until and unless I made AcknowledgeShipmentInbound node empty (deleting releaseID="9.2" xmlns="http://schema.infor.com/InforOAGIS/2" xmlns:xs="http://www.w3.org/2001/XMLSchema") select query is not fetching the value.

    Declare @xmlData xml
    set @xmlData = '<?xml version="1.0"?>
    <AcknowledgeShipmentInbound releaseID="9.2" xmlns="http://schema.infor.com/InforOAGIS/2" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema"> 
    <ApplicationArea>
        <Sender>
            <LogicalID>lid://ln01/2100</LogicalID>
            <ComponentID>erp</ComponentID>
            <ConfirmationCode>OnError</ConfirmationCode>
        </Sender>
        <CreationDateTime>2021-09-02T09:22:03Z</CreationDateTime>
        <BODID>1630574520554:123461:0</BODID>
     </ApplicationArea>
     <DataArea>
        <Acknowledge>
            <TenantID>KP_TRN</TenantID>
            <AccountingEntityID>2100</AccountingEntityID>
            <LocationID>S_2100</LocationID>
            <OriginalApplicationArea xmlns="">
                <Sender>
                    <LogicalID>oracle_erp_ihub_v2</LogicalID>
                    <ComponentID>External</ComponentID>
                    <ConfirmationCode>OnError</ConfirmationCode>
                </Sender>
                <CreationDateTime>2021-09-02T09:22:00.554Z</CreationDateTime>
                <BODID>ihub_v2:1630574520554:123461:0</BODID>
            </OriginalApplicationArea>
            <ResponseCriteria>
                <ResponseExpression actionCode="Rejected"/>
                <ChangeStatus>
                    <ReasonCode>tlbcts0074</ReasonCode>
                    <Reason languageID="en-US">Request validation; SHIPMENTS WITH REQUEST ID 
       2021090205 the value is too long.</Reason>
                </ChangeStatus>
            </ResponseCriteria>
        </Acknowledge>
        <ShipmentInbound>
            <ShipmentInboundHeader>
                <DocumentID>
                    <ID>Shipments with request id 28880902052200</ID>
                </DocumentID>
            </ShipmentInboundHeader>
            <ShipmentInboundLine>NONE</ShipmentInboundLine>
        </ShipmentInbound>
    </DataArea>
    </AcknowledgeShipmentInbound>'

   Declare @DocumentType nvarchar(100)
   DECLARE @DocumentId NVARCHAR(128);

   Select T.c.value('(DocumentID/ID/text())[1]', 'VARCHAR(128)')
   FROM @xmlData.nodes('*:AcknowledgeShipmentInbound/*:DataArea/*:ShipmentInbound/*:ShipmentInboundHeader') T(c)


            select T.c.query('./DocumentID/ID').value('.','nvarchar(128)')   as DocumentID
            from @xmlData.nodes('/AcknowledgeShipmentInbound/DataArea/ShipmentInbound/ShipmentInboundHeader') T(c)

CodePudding user response：

You need to define your namespace:

WITH XMLNAMESPACES (DEFAULT 'http://schema.infor.com/InforOAGIS/2')
SELECT SI.SIH.query('./DocumentID/ID').value('.', 'nvarchar(128)') AS DocumentID
FROM @xmlData.nodes('/AcknowledgeShipmentInbound/DataArea/ShipmentInbound/ShipmentInboundHeader') SI(SIH);