Trying to convert XML data into rows and columns using xmltable function

General
Enthusiast

Trying to convert XML data into rows and columns using xmltable function

Hi All,

 I have xml data from a web broser. I have to convert this data into rows and columns. I have created a table with column having xml data type.

 I have inserted this xml into this column. When i executed the below query using xmltable function, i getting no rows. Could you please check and let me know if anything i am doing wrong. I am pasting the query and xml data below, i have.

 query:

 SELECT X.*

 FROM (SELECT * FROM ttemp.work1 where id=1) AS C,

 XMLTable (

 '/linkcategories/linkcategory'

 PASSING c.gxml

 COLUMNS

 "seqno" for ORDINALITY,

 "id" integer PATH 'id',

 "name" varchar(200) PATH 'name',

 "des" varchar(400) path 'description'

 ) as x

Here is the sample data:

<?xml version="1.0" encoding="UTF-8" ?>

<linkCategories>

<linkCategory>

<description>lc for staus credits link in nights</description><id>3000000414</id><name>Account:Status_Credits</name>

<pattern>.*:account:statuscr</pattern></linkCategory><linkCategory>

<id>3000000394</id><name>Account:Tip10</name><pattern>.*tip-bronze-strive</pattern>

</linkCategory><linkCategory><id>3000000341</id><name>nights_ExclusiveOffer2</name>

<pattern>http://www.slogan.com.au/fly/dyn/memberSpecials/win-a-trip-around-the-world</pattern>

</linkCategory><linkCategory><id>3000000440</id><name>nights_10million</name>

<pattern>http://www.slogan.com.au/fly/dyn/memberSpecials/10million?alt_cam=au:ff:nights:160714:hero:10million...>

</linkCategory><linkCategory><id>3000000449</id><name>nights-qp-article</name>

<pattern>https://www.sloganpoints.com/earn-points/cars-hotels/hotels/ihg?alt_cam=au:ff:nights:150814:earn:IHG...>

</linkCategory><linkCategory><id>3000000452</id><name>nights-qp-account-hero</name><pattern>https://www.sloganpoints.com/fforsc/competitions</pattern>

</linkCategory><linkCategory><id>3000000453</id><name>nights_wherecouldigo</name><pattern>https://www.slogan.com.au/fly/do/dyns/auth/wherecouldigo/initialWhereCouldIGo?alt_cam=au:ff:nights</...>

</linkCategory><linkCategory><id>3000000457</id><name>nights_aquire_terms</name><pattern>https://www.aquire.com.au/terms</pattern></linkCategory>

<linkCategory><id>3000000458</id><name>nights_aquire_definationAirline</name><pattern>https://www.aquire.com.au/terms#definationAirline</pattern></linkCategory>

<linkCategory>

<id>3000000460</id>

<name>nights_benefits-of-gold</name>

<pattern>http://www.slogan.com.au/fly/dyn/flying/tier-benefits#benefits-of-gold</pattern>

</linkCategory>

<linkCategory>

<id>3000000461</id>

<name>nights_slogan_mona</name>

<pattern>http://www.slogan.com.au/fly/dyn/memberSpecials/mona</pattern>

</linkCategory>

<linkCategory>

<id>3000000462</id>

<name>nights_benefits-of-gold</name>

<pattern>http://www.slogan.com.au/fly/dyn/flying/tier-benefits#benefits-of-gold</pattern>

</linkCategory>

<linkCategory>

<id>3000000463</id>

<name>nights_benefits-of-silver</name>

<pattern>http://www.slogan.com.au/fly/dyn/flying/tier-benefits#benefits-of-silver</pattern>

</linkCategory>

<linkCategory>

<id>3000000464</id>

<name>nights_slogan_rockpool_gold_one</name>

<pattern>http://www.slogan.com.au/fly/dyn/memberSpecials/rockpool-gold-one</pattern>

</linkCategory>

<linkCategory>

<id>3000000465</id>

<name>nights_ticketmaster</name>

<pattern>http://www.ticketmaster.com.au/promo/l282rq</pattern>

</linkCategory>

<linkCategory>

<id>3000000468</id>

<name>nights_thekingandimusical</name>

<pattern>http://thekingandimusical.com.au/</pattern>

</linkCategory>

<linkCategory>

<id>3000000469</id>

<name>nights_ticketmaster_purchase</name>

<pattern>http://www.ticketmaster.com.au/h/purchase.html</pattern>

</linkCategory>

<linkCategory>

<id>3000000470</id>

<name>nights_slogangolfclub</name>

<pattern>http://www.slogan.com.au/fly/dyn/memberSpecials/slogangolfclub?utm_source=nights&amp;utm_medium=arti...>

</linkCategory>

<linkCategory>

<id>3000000471</id>

<name>nights_sydneytheatre_faqs</name>

<pattern>http://www.sydneytheatre.com.au/your-visit/how-to-book/faqs.aspx</pattern>

</linkCategory>

<linkCategory>

<id>3000000473</id>

<name>nights_teamchallenge_slogangolf</name>

<pattern>http://teamchallenge.slogangolf.com?utm_source=nights&amp;utm_medium=banner&amp;utm_campaign=qgcandn...>

</linkCategory>

<linkCategory>

<id>3000000474</id>

<name>nights_hhonors3_points</name>

<pattern>http://hhonors3.hilton.com/en/terms/index.html#accrual_of_points</pattern>

</linkCategory>

<linkCategory>

<id>3000000475</id>

<name>nights_hhonors3_3XsloganPoints</name>

<pattern>http://www.hhonors.com/3XsloganPoints</pattern>

</linkCategory>

<linkCategory>

<id>3000000476</id>

<name>nights_hhonors3_brands_index</name>

<pattern>http://hhonors3.hilton.com/en/explore/brands/index.html</pattern>

</linkCategory>

<linkCategory>

<id>3000000477</id>

<name>nights_hhonors3_terms_index</name>

<pattern>http://hhonors3.hilton.com/en/terms/index.html</pattern>

</linkCategory>

<linkCategory>

<id>3000000478</id>

<name>nights_ihg_slogan2x</name>

<pattern>http://www.ihg.com/slogan2x</pattern>

</linkCategory>

<linkCategory>

<id>3000000479</id>

<name>nights_ihg_rewardsclub_register</name>

<pattern>https://www.ihg.com/rewardsclub/us/en/join/register?cm_mmc=Partnership-_-6C_GB_en-_-QFF-_-Web1</patt...>

</linkCategory>

<linkCategory>

<id>3000000480</id>

<name>nights_motogp</name>

<pattern>http://www.motogp.com.au/qff?utm_source=qff&amp;utm_medium=eDM&amp;utm_campaign=slogan?alt_cam=au:ff...>

</linkCategory>

<linkCategory>

<id>3000000481</id>

<name>nights_ihgrewardsclub</name>

<pattern>http://www.ihgrewardsclub.com/</pattern>

</linkCategory>

<linkCategory>

<id>3000000482</id>

<name>nights_ticketmaster_promo</name>

<pattern>http://www.ticketmaster.com.au/promo/l282rq?alt_cam=au:ff:nights:150814:exclusive:the-king-and-i</pa...>

</linkCategory>

<linkCategory>

<id>3000000483</id>

<name>nights_airlines_sloganclub</name>

<pattern>http://www.slogan.com.au/travel/airlines/slogan-club/global/en</pattern>

</linkCategory>

<linkCategory>

<id>3000000484</id>

<name>nights_slogan_sydney-theatre-company</name>

<pattern>http://www.slogan.com.au/fly/dyn/memberSpecials/sydney-theatre-company</pattern>

</linkCategory>

<linkCategory>

<id>3000000485</id>

<name>nights_jetstar_media</name>

<pattern>http://www.jetstar.com/au/en/~/_media/BF9BD5F055D4451CBA12789F9A22D871.pdf</pattern>

</linkCategory>

<linkCategory>

<id>3000000486</id>

<name>nights_sloganright_join</name>

<pattern>https://www.sloganright.com.au/join</pattern>

</linkCategory>

<linkCategory>

<id>3000000487</id>

<name>nights_namkarnballet</name>

<pattern>https://www.namkarnballet.com.au/whats_on/event_detail?prodid=5332</pattern>

</linkCategory>

<linkCategory>

<id>3000000489</id>

<name>nights_restaurants-nights-august-2014_fathers-day</name>

<pattern>http://restaurants.slogan.com.au/links/nights-august-2014?alt_cam=au:ff:nights:150814:earn:restauran...>

</linkCategory>

<linkCategory>

<id>3000000490</id>

<name>nights_restaurants-fathers-day</name>

<pattern>http://restaurants.slogan.com.au/?alt_cam=au:ff:nights:150814:earn:restaurants-fathers-day</pattern>

</linkCategory>

<linkCategory>

<id>3000000494</id>

<name>nights_slogan_airlines_hotels</name>

<pattern>http://www.slogan.com.au/travel/airlines/hotels/global/en</pattern>

</linkCategory>

<linkCategory>

<id>3000000495</id>

<name>nights_slogan_hotel</name>

<pattern>http://www.slogan.com.au/fly/dyn/partners/hotel</pattern>

</linkCategory>

<linkCategory>

<id>3000000496</id>

<name>nights_slogan_airline_earnings</name>

<pattern>http://www.slogan.com.au//fly/dyn/flying/airline-earning</pattern>

</linkCategory>

<linkCategory>

<id>3000000497</id>

<name>nights_slogan_million_comp_terms</name>

<pattern>http://slogan.com.au/fly/dyn/memberSpecials/million-comp-terms</pattern>

</linkCategory>

<linkCategory>

<id>3000000498</id>

<name>nights_sloganright_sloganawards</name>

<pattern>https://www.sloganright.com.au/article/100011-8796420790762?utm_source=nights0814&amp;utm_medium=ban...>

</linkCategory>

<linkCategory>

<id>3000000499</id>

<name>nights_wotf.aww</name>

<pattern>http://www.wotf.aww.com.au</pattern>

</linkCategory>

<linkCategory>

<id>3000000500</id>

<name>nights_sloganright_winecompanion</name>

<pattern>https://www.sloganright.com.au/Events/c/ecat107?q=%3Aevent-asc%3AeventDateRange%3Aupcoming&amp;show=...>

</linkCategory>

<linkCategory>

<id>3000000501</id>

<name>nights_slogan_rewards-credit-cards</name>

<pattern>http://www.slogan.com.au/fly/dyn/memberSpecials/cards/rewards-credit-cards?int_cam=ff:promo:earn-slo...>

</linkCategory>

<linkCategory>

<id>3000000502</id>

<name>nights_jetstar-gold-mastercard</name>

<pattern>http://www.slogan.com.au/fly/dyn/memberSpecials/cards/jetstar-gold?alt_cam=au:ff:nights:150814:earn:...>

</linkCategory>

<linkCategory>

<id>3000000504</id>

<name>nights_2014winecompanionawards</name>

<pattern>http://www.sloganright.com.au/2014winecompanionawards?utm_source=nights&amp;utm_medium=nm-intro&amp;...>

</linkCategory>

<linkCategory>

<id>3000000505</id>

<name>nights_2015winecompanionawards_article</name>

<pattern>http://www.sloganright.com.au/2015winecompanionawards?utm_source=nights&amp;utm_medium=nm-article&am...>

</linkCategory>

<linkCategory>

<id>3000000506</id>

<name>nights_2015winecompanionawards_product</name>

<pattern>http://www.sloganright.com.au/2015winecompanionawards?utm_source=nights&amp;utm_medium=nm-product&am...>

</linkCategory>

</linkCategories>

Thanks,

 Pavan

 C.kothamasu@gmail.com

2 REPLIES
amy
Teradata Employee

Re: Trying to convert XML data into rows and columns using xmltable function

Hi Pavan,

I could get answer with the SQL below:

SELECT X.*

FROM (SELECT * FROM xml_test WHERE id=1) AS C,

XMLTable (

  '/linkCategories/linkCategory'

PASSING c.xmldoc

COLUMNS

"seqno" for ORDINALITY,

"id" decimal(12,0) PATH 'id',

"name" varchar(200) PATH 'name',

"des" varcar(400) PATH 'pattern'

) as X;

The '/linkCategories/linkCategory' is case specific and id does not fit in Integer, so I changed it to decimal.

Amy

Enthusiast

Re: Trying to convert XML data into rows and columns using xmltable function

Hi Amy,

Thanks for your response.

While working with the XML data type. I am getting the Error "Error in function XML Table:Error Translating column value to target data typeReached LOB segments threshold limit."

 

Can any one have idea, how to work on this error.



Thanks & Regards,

Pavan