Link to home
Start Free TrialLog in
Avatar of Larry Groves
Larry GrovesFlag for United States of America

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...

<?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>
...

Open in new window


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;

Open in new window


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

Open in new window


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-catalog' 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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Larry Groves

ASKER

Thank you so much slightwv. I completely overlooked that.

Thanks,
Larry