XML z SQL Server

0

Otázka

zkusil níže, ale dokud a pokud jsem udělal AcknowledgeShipmentInbound uzlu prázdné (vymazání releaseID="9.2" xmlns="http://schema.infor.com/InforOAGIS/2" xmlns:xs="http://www.w3.org/2001/XMLSchema") select dotazu není načítání hodnoty.

    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)
sql-server xml
2021-11-23 15:46:45
1

Nejlepší odpověď

1

Je třeba definovat jmenný prostor:

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);
2021-11-23 15:53:49

V jiných jazycích

Tato stránka je v jiných jazycích

Русский
..................................................................................................................
Italiano
..................................................................................................................
Polski
..................................................................................................................
Română
..................................................................................................................
한국어
..................................................................................................................
हिन्दी
..................................................................................................................
Français
..................................................................................................................
Türk
..................................................................................................................
Português
..................................................................................................................
ไทย
..................................................................................................................
中文
..................................................................................................................
Español
..................................................................................................................
Slovenský
..................................................................................................................