All Forums General
PAVAN1K 6 posts Joined 09/14
20 Aug 2015
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</pattern>
</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-double-points-20fb</pattern></ linkCategory><linkCategory><id>3000000451</ id><name>nights_aquire_offer</name><pattern>https:// www.aquire.com.au/register</pattern>
</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</pattern></ linkCategory><linkCategory><id>3000000454</ id><name>nights_ladygaga</name><pattern>http:// premier.ticketek.com.au/shows/show.aspx?pp=QFITT&amp; sh=LADYGAGA14</pattern></ linkCategory><linkCategory><id>3000000455</ id><name>nights_benefits-of-gold-one</ name><pattern>http://www.slogan.com.au/fly/dyn/flying/ tier-benefits#benefits-of-gold-one</pattern>
</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=article&amp;utm_campaign=qgcandnz</ pattern>
</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=qgcandnz</pattern>
</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</ pattern>
</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:nights:150814: exclusive:motogp</pattern>
</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</ pattern>
</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: restaurants-fathers-day</pattern>
</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=banner&amp;utm_campaign=sloganawards</ pattern>
</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=All&amp;utm_source=nights&amp; utm_medium=m-banner&amp;utm_campaign=winecompanion</ pattern>
</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-slogan-points-on-everyday-spend:lang:en</ pattern>
</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:jetstar-gold-mastercard-offer</ pattern>
</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;utm_campaign=winecompanion</ pattern>
</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&amp;utm_campaign=winecompanion</ pattern>
</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&amp;utm_campaign=winecompanion</ pattern>
</linkCategory>
</linkCategories>
 
 
Thanks,
 Pavan
 C.kothamasu@gmail.com
 

Pavan
amy 1 post Joined 02/11
22 Feb 2016

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
 

PAVAN1K 6 posts Joined 09/14
19 Mar 2016

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

Pavan

You must sign in to leave a comment.