XML from SQL Server

Question

在下面尝试过，但直到并且除非我将AcknowledgeShipmentInbound节点设为空(删除releaseID＝"；9.2〃；xmlns＝"；http://schema.infor.com/InforOAGIS/2"xmlns:xs="http://www.w3.org/2001/XMLSchema"(select查询未获取值。

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)

Answer 1

您需要定义您的命名空间：

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);