sql - XML 解析和 T-SQL——第 3 部分

Question

返回的xml，部分如下:

<Order xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:d1p2="http://schemas.abccompany.com/oml/package/1.0" xmlns:d1p1="http://schemas.abccompany.com/oml/batch/1.0" xmlns="http://schemas.abccompany.com/oml/base/1.0" intendedUse="0" quoteBack="5062-JA$181-3282" d1p1:transactionId="00000000-0000-0000-0000-000000000000" d1p2:uri="asdfasd-afdadfs-adsasdf" d1p1:customerId="0" d1p1:userId="0" d1p1:enabled="false" d1p1:priority="Low" d1p1:frequency="0" d1p1:recordCount="0" d1p1:executionPeriod="Once">
  <Security xmlns="http://schemas.abccompany.com/oml/security/1.0">
    <RootCredentials username="ust_3dResults2" password="1234567" />
    <LocationID>abcd</LocationID>
    <AccountID>9876</AccountID>
    <CustomerUserReferenceID>ssmart</CustomerUserReferenceID>
  </Security>
</Order>

例如，使用 t-sql，我将如何提取“AccountID”？我试过:

;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema' AS xsd, DEFAULT 'http://www.w3.org/2001/XMLSchema')
SELECT adr.id, 
adr.omlinput,
adr.omlinput.value('(/Order/Security/AccountID)[1]', 'varchar(50)') AS [Results]
FROM [Reporting].[ApplicantDirectRequest] adr WITH (NOLOCK)
WHERE adr.OmlInput IS NOT NULL

和

;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema' AS xsd, DEFAULT 'http://www.w3.org/2001/XMLSchema')
SELECT adr.id, 
adr.omlinput,
adr.omlinput.value('(/xsd:Order/Security/AccountID)[1]', 'varchar(50)') AS [Results]
FROM [Reporting].[ApplicantDirectRequest] adr WITH (NOLOCK)
WHERE adr.OmlInput IS NOT NULL

Answer 1

试试这个:

;WITH xmlnamespaces('http://schemas.abccompany.com/oml/security/1.0' AS ns, 
                    'http://schemas.abccompany.com/oml/base/1.0' AS base)
SELECT 
    adr.id, 
    adr.omlinput.value('(/base:Order/ns:Security/ns:AccountID)[1]', 'int') AS [Results]
FROM 
    [Reporting].[ApplicantDirectRequest] adr
WHERE 
    ID = 1

棘手的部分是:<Order>元素有 base命名空间，而它下面的所有内容(<Security> 和 <AccountID> 元素)都有 ns命名空间。

由于没有 namespace 扩展到整个 XML 片段，您不能真正使用默认 namespace ....