XML解析和T-SQL -- 第三部分

Question

以下是XML返回的部分内容：

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

- John Waclawski

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命名空间。

由于没有命名空间扩展到整个XML片段，因此您不能真正使用默认命名空间....

- marc_s

---

做得好，马克！如果可以麻烦您解释一下您是如何想出这个解决方案的，那对我来说将是一个额外的奖励。 - John Waclawski

@JohnWaclawski：你必须仔细查看定义的命名空间。那些没有前缀的将自动应用于它们所定义的XML元素（以及它们下面的任何内容，除非被覆盖）。然后检查所有带有前缀的命名空间，并查看它们应用于XML元素的位置。那些在任何地方都没有使用的命名空间（例如你的情况中带有xsi和xsd前缀的命名空间）可以省略 - 其他需要被定义。 - marc_s

如果我的xml中有一个元素...例如： <Subject key="" package-uri="" role="Primary"> 那么如何将其合并到我的t-sql中呢？ 假设它是这样的： <Subjects> <Subject key="" package-uri="" role="Primary"> <FirstName>Ron</FirstName> <LastName>Burgandy</LastName> </Subject> </Subjects> - John Waclawski

@JohnWaclawski：如果一个XML元素没有定义默认的XML命名空间（xmlns="..."没有任何前缀），并且它本身也没有前缀，那么它就是属于其父节点所适用的XML命名空间（该命名空间可能不是直接在父节点上定义的，而是在更高层次上定义的）。 - marc_s