XMLEXTRACT causing issues for a varchar column to be primary index after conversion

Extensibility
Highlighted
Enthusiast

XMLEXTRACT causing issues for a varchar column to be primary index after conversion

Hello Gurus,

I am facing an issue while extracting an element from XML document and using that as primary index for the table. We are getting online orders information and our order id is burried in the xml as a first element. something like this.

<Order> <onlineorderid>ABC123456</onlineorderid>. I am trying to fetch the value and use it as my primary index in the table. 

I am using XMLEXTRACT function and able to fetch that column as VARCHAR(30) UNICODE but when i am trying to use that column as my index it is complaining that LOB's are not allowed in the index. If use XMLTABLE i can parse the columns in the respective SQL data types and use them. Not sure why XMLEXTRACT is giving me the problems here. Any information would be greatly appreciated. I tried transalting the column into latin as well, but no use.

 

SyntaxEditor Code Snippet

create volatile table vt_temp as(select CREATEXML('
    <Order>
    <OnlineOrderId>100001000000010</OnlineOrderId>
    <PartsAmount>9.99 </PartsAmount>
    <LaborAmount>10.99</LaborAmount>
       <Item>
       <itemnumber>1234</itemnumber>
       <liastprice>10.00</listprice>
        </Item>
    </Order>
    ') AS orderinfo,
    CAST(CAST(orderinfo.XMLEXTRACT('Order/OnlineOrderId',NULL) AS VARCHAR(30) CHARACTER SET UNICODE) AS VARCHAR(30))  onlineorderid,
    TRANSLATE(onlineorderid USING UNICODE_TO_LATIN) onlinekeyid)with data primary index(onlinekeyid)on commit preserve rows
    

Appreciate your help in this regard. 

Thanks

Siva