XML 处理，如何检查 SQL Server 中的元素中是否包含属性

Question

下面是示例 XML 字符串：

<R  RMA="1"   UsrID="AXxxx"  FirstName="xx" LastName="yy" Email="dd" title="">  
<Contract ContractNr="xxx1" ETC=""  TermRC=""  DESC=""  INVCOMMENT="" TermDate="03/01/2014" PO="XXX" DestRule="xxxx">     
<Asset ID="12345" AXID="abcde"   RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
<Asset ID="67890" AXID="abcde1"  RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
</Contract>
<Contract ContractNr="xxx2" ETC=""  TermRC=""  DESC=""  INVCOMMENT=""  TermDate="03/01/2014" DestRule="xxxx">    
<Asset ID="54321" AXID="edcba"   RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID="" />
<Asset ID="09876" AXID="edcba1"  RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID="" />
</Contract>
<Contract ContractNr="xxx1" ETC=""  TermRC=""  DESC=""  INVCOMMENT="" TermDate="03/01/2014" PO="XXX" >     
<Asset ID="12345" AXID="abcde"   RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
<Asset ID="67890" AXID="abcde1"  RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
</Contract>    
<Contract ContractNr="xxx1" ETC=""  TermRC=""  DESC=""  INVCOMMENT="" TermDate="03/01/2014" >     
<Asset ID="12345" AXID="abcde"   RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
<Asset ID="67890" AXID="abcde1"  RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
</Contract>    
</R>

我想检查是否所有元素都包含 PO 和 DestRule 属性，如果没有，则打印 false，否则打印 true。

SQL服务器能做到吗，SQL语句怎么写？

Answer 1

试试这个：

DECLARE @xml        xml = '<R  RMA="1"   UsrID="AXxxx"  FirstName="xx" LastName="yy" Email="dd" title="">  
<Contract ContractNr="xxx1" ETC=""  TermRC=""  DESC=""  INVCOMMENT="" TermDate="03/01/2014" PO="XXX" DestRule="xxxx">     
<Asset ID="12345" AXID="abcde"   RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
<Asset ID="67890" AXID="abcde1"  RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
</Contract>
<Contract ContractNr="xxx2" ETC=""  TermRC=""  DESC=""  INVCOMMENT=""  TermDate="03/01/2014" DestRule="xxxx">    
<Asset ID="54321" AXID="edcba"   RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID="" />
<Asset ID="09876" AXID="edcba1"  RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID="" />
</Contract>
<Contract ContractNr="xxx1" ETC=""  TermRC=""  DESC=""  INVCOMMENT="" TermDate="03/01/2014" PO="XXX" >     
<Asset ID="12345" AXID="abcde"   RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
<Asset ID="67890" AXID="abcde1"  RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
</Contract>    
<Contract ContractNr="xxx1" ETC=""  TermRC=""  DESC=""  INVCOMMENT="" TermDate="03/01/2014" >     
<Asset ID="12345" AXID="abcde"   RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
<Asset ID="67890" AXID="abcde1"  RecvdDt="05/02/2014" ReconDt="05/02/2014" ReceiptID=""/>
</Contract>    
</R>'
IF EXISTS
(
SELECT      NULL
FROM       @xml.nodes('/R/Contract')   X(Contract)
WHERE      Contract.value('@PO', 'varchar') IS NULL
OR  Contract.value('@DestRule', 'varchar') IS NULL
)
BEGIN
PRINT 'some contracts do not have PO or DestRule'
END
ELSE
BEGIN
PRINT 'OK'
END