xml - 从 Oracle 数据库创建 XML 文件
问题描述
我正在使用 Oracle 数据库。我需要创建一个从某些表中检索数据的 XML 文件。我编写了一个查询,它为我提供了所需的 XML 结果。请参阅下面的查询。
现在,我需要:
将查询写入“脚本形式文件”,以便我可以从应用程序内部执行该文件。
将参数传递给脚本文件(以选择我想要从中获取数据的正确记录 - 在我提供的查询中,这是修复值“E_0015”)。
将查询结果保存在 XML 文件中。
我怎样才能实现所有这些目标?
select
XMLSerialize(DOCUMENT
XMLElement("Gapi",
XMLAttributes('http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi",
'http://www.hmpwjb.com/gapi/WorkOrder' as "xmlns",
'TIW000' as "source_reference",
'WorkOrder' as "name",
'TIW17FNQ' as "destination_reference"),
(
select XMLElement("WorkOrderHeader",
XMLElement("SourceTradingPartner", 'TIW000'),
XMLElement("DestinationTradingPartner", 'TIW17FNQ'),
XMLElement("WorkOrder", WorkOrderHeader.WO_NUM),
XMLElement("WorkOrderDescription", WorkOrderHeader.WO_DESCRIPTION),
XMLElement("ResponsiblePersonID", 'Opr'),
XMLElement("DivisionID", 'COUNTRY'),
XMLElement("SynchIndicatorID", 'A')
, (
select XMLElement("WorkOrderLine",
XMLElement("WorkOrderLineReference", '0001'),
XMLElement("Item", 'ITEM_CODE'),
XMLElement("SynchIndicatorID", 'A'),
XMLElement("SubstitutePriorityMethod", 'Y')
, (
select XMLElement("WorkOrderRoutingHeader",
XMLElement("SynchIndicatorID", 'A'),
XMLElement("Increment", '10')
, (
select XMLElement("WorkOrderRouting",
XMLElement("SynchIndicatorID", 'A'),
XMLElement("Operation", ' 10'),
XMLElement("Function", 'FUNCTION'),
XMLElement("OperationType", 'I'),
XMLElement("WorkCenter", wortg.WC)
, (
select XMLAgg(XMLElement("WorkOrderRoutingTool",
XMLForest('A' as "SynchIndicatorID",
WorkOrderRoutingTool.WO_RTOOL_SEQ as "ToolSeq",
WorkOrderRoutingTool.TOOL as "ToolID",
cast(WorkOrderRoutingTool.QTY as varchar(100)) as "ToolQuantity"))
order by WorkOrderRoutingTool.WO_RTOOL_SEQ
)
from WO_RTOOL WorkOrderRoutingTool
where WorkOrderRoutingTool.WO_NUM = WorkOrderHeader.WO_NUM
)
)
from WO_HDR WorkOrderRouting
left outer join WO_RTG wortg
on wortg.CCN = WorkOrderRouting.CCN and wortg.MAS_LOC = WorkOrderRouting.MAS_LOC and
wortg.WO_NUM = WorkOrderRouting.WO_NUM
where WorkOrderRouting.WO_NUM = WorkOrderHeader.WO_NUM
)
, (
select XMLElement("WorkOrderRoutingAddendum",
XMLElement("SynchIndicatorID", 'A'))
from WO_HDR WorkOrderRoutingAddendum
where WorkOrderRoutingAddendum.WO_NUM = WorkOrderHeader.WO_NUM
)
)
from WO_HDR WorkOrderRoutingHeader
where WorkOrderRoutingHeader.WO_NUM = WorkOrderHeader.WO_NUM
)
)
from WO_HDR WorkOrderLine
where WorkOrderLine.WO_NUM = WorkOrderHeader.WO_NUM
)
,(
select XMLElement("WorkOrderLineAddendum",
XMLElement("SynchIndicatorID", 'A'),
XMLElement("PlanningStatus", '4'),
XMLElement("ManufacturingStatus", '2'))
from WO_HDR WorkOrderLineAddendum
where WorkOrderLineAddendum.WO_NUM = WorkOrderHeader.WO_NUM
)
)
from WO_HDR WorkOrderHeader
where WorkOrderHeader.WO_NUM = ' E_0015'
)
)
AS BLOB ENCODING 'utf-8' VERSION '1.0' INDENT) as "XML"
from dual;
解决方案
推荐阅读
- powerapps - 如何在 powerapps 中打开文件或文档的 ac:// 文件夹
- elasticsearch - 执行汇总作业时映射异常
- excel - VBA 项目资源管理器中未显示表
- rust - 如何在 Rust 中实现作用域异步任务?
- c# - 为 KeyValuePair 赋值
- scala - 如何使用动态字段对 List[HashMap[String, Any]] 进行排序
- powerquery - 使用 POST 请求获取所有项目时出现 400 错误请求
- python-3.x - 字符串文本到 CSV
- html - 使用 iFrame 加载本地 html 文件
- git - 通过 git review 向 gerrit 提交补丁集时远程被拒绝错误