重命名 XML 输出中的标签
我有一些代码可以从一些表生成 XML。我正在查询的表是从 XSD 文件生成的，该文件描述了我应该生成的 XML。问题在于表和字段的名称遵循与架构中的名称不同的命名约定 - 例如，架构中名为“personID”的元素在数据库表中将是“PRSN_ID”。为了生成输出，我有如下所示的代码：
Select xmlelement("person",
xmlelement("personID",PRSN_ID),
xmlelement("personName",PRSN_NAM),
...
正如您可以想象的，这开始变得乏味。我正在寻找更好的解决方案。我知道我可以使用 xmlforest 为标签指定与所选列相同的名称，现在我需要一种翻译方法。例如，如果我生成：
/*the full long list of columns names can be generated so it's an easy copy-paste:*/
select xmlelement("PRSN", xmlforest(PRSN_ID,PRSN_NAM,...
/*produces this:*/
<PRSN>
<PRSN_ID>1<PRSN_ID/>
<PRSN_NAM>BOB<PRSN_NAM/>
...
我需要将其翻译为：
<person>
<personID>1</personID>
<personName>BOB</personName>
...
我将如何在 Oracle PL/SQL 中进行这种翻译？我确实有一个生成的映射文件，它告诉我“PRSN_ID”应该翻译为“personID”，我只是不确定继续执行此操作的最佳方法。
I have some code that generates XML from some tables. The tables that I'm querying were generated from an XSD file the describes the XML I am supposed to be generating. The problem is that the names of the tables and fields follow a different naming convention than those in the schema - for example, an element named "personID" in the schema would be "PRSN_ID" in the database tables. To generate output, I have code that looks like this:
Select xmlelement("person",
xmlelement("personID",PRSN_ID),
xmlelement("personName",PRSN_NAM),
...
As you can imagine, this is starting to get tedious. I am looking for a better solution. I know I can use xmlforest to give tags the same names as a selected column, and I now need a way to translate. For example, if I generated:
/*the full long list of columns names can be generated so it's an easy copy-paste:*/
select xmlelement("PRSN", xmlforest(PRSN_ID,PRSN_NAM,...
/*produces this:*/
<PRSN>
<PRSN_ID>1<PRSN_ID/>
<PRSN_NAM>BOB<PRSN_NAM/>
...
I would need to translate it into this:
<person>
<personID>1</personID>
<personName>BOB</personName>
...
How would I go about doing this kind of translation in Oracle PL/SQL? I do have a generated mapping file that tells me that "PRSN_ID" should be translated to "personID", I'm just not sure the best way to proceed with this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您不能在重命名列的表上创建视图吗？如果将列名称放在双引号中，Oracle 将混合大小写列名称
Can't you create view over the table with the columns renamed. Oracle will do mixed case column names if you put them in double quotes
如果您有一个生成的映射文件，其中指出 PRSN_ID 应该是 personID，那么为什么不使用该映射信息生成第一个 SELECT 命令呢？
如果该 SELECT 命令满足您的要求，那么生成它就可以消除乏味。
If you have a generated mapping file that says PRSN_ID should be personID, why don't you just generate your first SELECT command using that mapping information?
If that SELECT command does what you want, generating it takes away the tedium.