Larry Groves
asked on
Oracle XML error: ORA-19208: parameter 2 of function XMLELEMENT must be aliased
Hello. Please forgive if this has been answered already. I've searched but couldn't find an answer though. I'm pretty new to XML in Oracle and I'm trying to create an XML extract from an Oracle 12c DB. Here is an example output that I'm trying to accomplish...
This is what I've come up with so far...
When I run this I'm getting the following error:
I'm really not sure what exactly needs to be aliased. I'm not using XMLForest or anything and I did add an alias to the XMLRoot. Maybe the error I'm getting is because of something else I'm doing wrong. Is it even possible to have a hard-coded value in XMLAttributes (e.g. the "master-catalog" in XMLElement("catalog", XMLAttributes('master-cata log' AS "catalog-id"))? Or, just maybe, there is a much better way for me to get the result I need.
Hopefully you can help me out.
Thanks,
Larry
<?xml version="1.0" encoding="UTF-8"?>
<catalog catalog-id="master-catalog>
<header>
<image-settings>
<external-location>
<http-url>http</http-url>
<https-url>https</https-url>
</external-location>
<view-types>
<view-type>large</view-type>
<view-type>medium</view-type>
<view-type>small</view-type>
<view-type>swatch</view-type>
</view-types>
<alt-pattern>${productname}</alt-pattern>
<title-pattern>${productname}</title-pattern>
</image-settings>
</header>
<product product-id="0">
<ean/>
<upc>0</upc>
<unit/>
<min-order-quantity>1</min-order-quantity>
<step-quantity>1</step-quantity>
<store-force-price-flag>false</store-force-price-flag>
<store-non-inventory-flag>false</store-non-inventory-flag>
<store-non-revenue-flag>false</store-non-revenue-flag>
...
This is what I've come up with so far...
SELECT XMLRoot(
XMLElement("catalog", XMLAttributes('master-catalog' AS "catalog-id"),
XMLElement("header",
XMLElement("image-settings",
XMLElement("external-location",
XMLElement("http-url", 'http/'),
XMLElement("https-url", 'https')
),
XMLElement("view-types",
XMLElement("view-type",'large'),
XMLElement("view-type",'medium'),
XMLElement("view-type",'small'),
XMLElement("view-type",'swatch')
),
XMLElement("alt-pattern",'${productname}'),
XMLElement("title-pattern",'${productname}')
)
),
XMLElement("category", XMLAttributes("category-id", 'root'),
XMLElement("online-flag",'true')
),
XMLAgg(
XMLElement("product", XMLAttributes(p.part_number AS "product-id"))
)
), VERSION '1.0', STANDALONE YES).getClobVal() as XMLDATA
from products p
where p.buyable = 1;
When I run this I'm getting the following error:
ORA-19208: parameter 2 of function XMLELEMENT must be aliased
19208. 00000 - "parameter %s of function %s must be aliased"
*Cause: The indicated parameter of the XML generation function has not been aliased, although it is an expression.
*Action: Specify an alias for the expression using the AS clause.
Error at Line: 26 Column: 15
I'm really not sure what exactly needs to be aliased. I'm not using XMLForest or anything and I did add an alias to the XMLRoot. Maybe the error I'm getting is because of something else I'm doing wrong. Is it even possible to have a hard-coded value in XMLAttributes (e.g. the "master-catalog" in XMLElement("catalog", XMLAttributes('master-cata
Hopefully you can help me out.
Thanks,
Larry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks,
Larry