得到了多项目序列

问题描述

我的目的是选择容器号等于输入数据的数据"(某种搜索功能).当我尝试检索数据时遇到一个问题，在什么情况下出现问题:

My intention is to "select the data where container number is equals to input data" (kind of search functionality). I am facing an issue when I am trying to retrieve the data, with a problem at where condition:

d:goodsShipments/d:consignment/d:transportEquipment/d:id/text()

这里我正在获取多个数据.我不知道如何在where条件下进行迭代.

Here I am getting multiple data. I don't know how to iterate it in the where condition.

我的查询是:

我的源XML是:

我如何选择sealId为5678的所有声明?在这种情况下如何处理where条件?

How i can select all the declaration where sealId is 5678?how deal with the where condition in this case?

推荐答案

每个货物中有多个容器，并且在从原始XML中提取了容器之后，您将基于LRN进行过滤；因此，您需要使用嵌套的XMLTable对象.第一个从声明中获取数据，并将货物作为子XMLType提取.然后将其传递给第二个XMLTable，后者提取容器信息.

You have multiple containers per consignment, and you're filtering based on LRN after extracting that from the raw XML; so you need to use nested XMLTable objects. The first one gets the data from the declaration and extracts the consignments as a sub-XMLType. That is then passed to the second XMLTable which extracts the container information.

SELECT x1.lrn, x1.username, x2.containerNumber
FROM dmsimport_decl d
CROSS JOIN XMLTable(
  XMLNAMESPACES(DEFAULT 'http://www.xxxx.invalid/xxxx/xxx/schema/xxx',
    'http://www.xxxx.invalid/xxx/schema/common' AS "c",
    'http://www.xxxx.invalid/xxxx/xxx/schema/xxx' AS "d"),
  '/d:declaration'
  PASSING d.object_value
  COLUMNS
    lrn VARCHAR2(35 CHAR)
      PATH 'c:declarationHeader/c:localReferenceNumber/text()',
    username CHAR(25)
      PATH 'c:declarationHeader/c:username/text()',
    consignment XMLType
      PATH 'd:goodsShipments/d:consignment'
) x1
CROSS JOIN XMLTable(
  XMLNAMESPACES(DEFAULT 'http://www.xxxx.invalid/xxxx/xxx/schema/xxx',
    'http://www.xxxx.invalid/xxx/schema/common' AS "c",
    'http://www.xxxx.invalid/xxxx/xxx/schema/xxx' AS "d"),
  '//d:transportEquipment'
  PASSING x1.consignment
  COLUMNS
    containerNumber VARCHAR2(35 CHAR)
      PATH 'd:id/text()'
) x2
WHERE x1.lrn = 'NLDMS111111150010950';

使用您的(更新的)示例XML，将产生:

With your (updated) sample XML, that produces:

LRN                                 USERNAME                  CONTAINERNUMBER
----------------------------------- ------------------------- -----------------------------------
NLDMS111111150010950                testSC testSC             abcd
NLDMS111111150010950                testSC testSC             bcde
NLDMS111111150010950                testSC testSC             cdef
NLDMS111111150010950                testSC testSC             defg
NLDMS111111150010950                testSC testSC             efgh

希望这就是您想要看到的.

Hopefully that is what you want to see.

Quick SQL Fiddle演示.

您还可以使用更复杂的XPath将其保存在单个XMLTable中，但是我认为这很清楚.

You could also use a more complicated XPath to keep it within a single XMLTable, but I think this is clearer.

这篇关于得到了多项目序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！