Oracle SQL Generate nested xml -


i want nested xml using 1 query in plsql function oracle.

the database (cannot changed):

table 'products_details':

`attr_id` | `attribute`    | `fk_parent_id`(foreign key on `attr_id`) ------------------------------------------------------------------------------- 1         |  name           | null 3         |  sizes          | null 4         |  size_women     | 3 5         |  size_man       | 3 6         |  size_dimension | 3

table 'product_contents':

`detail`      | `value`   |  variation_number  | `product_id` (doesnt matter) ------------------------------------------------------------------------------- name          |  tshirt   | null               | 1000 price         |  14.99    | null               | 1000 size_man      |  xl       | 1                  | 1000 size_women    |  l        | 1                  | 1000 size_dimesion |  21x25cm  | 1                  | 1000 size_man      |  m        | 2                  | 1000 size_women    |  s        | 2                  | 1000 size_dimesion |  14x16cm  | 2                  | 1000 ...

as can see there options (name, price) once each product there options (size_man, size_woman...) variations , can exist more 1 time each product.

what want xml:

<attribute detail="name">tshirt</attribute> <attribute detail="price">14.99</attribute> <attribute detail="sizes">       <row variation_number="1">           <attribute detail="size_man">xl</attribute>           <attribute detail="size_women">l</attribute>           ...       </row>       <row variation_number="2">           <attribute detail="size_man">m</attribute>           <attribute detail="size_women">s</attribute>       </row> </attribute>

what tried far (which of course not working):

select  (     xmlelement(  "attribute",                 xmlattributes(pc.detail "detail"),                 (select xmlagg                 (                  xmlelement("row", xmlattributes(pc.variant_number "variation_number") )                  )                  product_contents pc                   join product_details pd on pc.detail = pd.attribute , pc.product_id = '1000'                  pd.fk_parent_id = pd.id                )        ).getclobval() content  product_details pd    pd.fk_parent_id null order pd.attribute;

how can single query?

here are:

with   -- "memory table"   product_details (     select 1 attr_id, 'name' attr, null parent_id dual union      select 2, 'price', null dual union      select 3, 'sizes', null dual union      select 4, 'size_women', 3 dual union      select 5, 'size_man', 3 dual union      select 6, 'size_dimension', 3 dual   ),   -- "memory table"   product_contents (     select 'name' detail, 'tshirt' value, null variation, 1000 product_id dual union     select 'price', '14.99', null, 1000 dual union     select 'size_man', 'xl', 1, 1000 dual union     select 'size_women', 'l', 1, 1000 dual union     select 'size_dimesion', '21x25cm', 1, 1000 dual union     select 'size_man', 'm', 2, 1000 dual union     select 'size_women', 's', 2, 1000 dual union     select 'size_dimesion', '14x16cm', 2, 1000 dual   ),   product_contents_xml (     select          variation,       detail,       xmlelement(         "attribute",         xmlattributes(detail "detail"),         value       ) attr,       product_id     product_contents   ),   attrs (       select         pc.attr               product_contents_xml pc join          product_details pd on pc.detail = pd.attr , pc.product_id = 1000               pd.parent_id null        union      select       xmlelement("attribute", xmlattributes(t.attr "detail"), xmlagg(t.value)) attr     (             select             parent.attr,             xmlelement("row", xmlattributes(pc.variation "variation_number"), xmlagg(pc.attr)) value                       product_contents_xml pc join              product_details pd on pc.detail = pd.attr , pc.product_id = 1000 join             product_details parent on parent.attr_id = pd.parent_id                       pd.parent_id not null           group             parent.attr, pc.variation     ) t     group t.attr   ) select xmlagg(attr) attrs

attrs view splited 2 parts - 1 attributes without parent_id , 1 parent_id.


Comments

Post a Comment

Popular posts from this blog

c++ - CryptStringToBinary API behavior -

i seeing strange behavior cryptstringtobinary cryptography api. please see below code (config: x64 debug): #include "stdafx.h" #include <windows.h> #include <strsafe.h> #include <iostream> #include <exception> void main() { dword dwskip; dword dwflags; dword dwdatalen; //lpcwstr pszinput = l"jaaaaaecaaadzgaaakqaagdnnl1l56bwgfjdgr3rpxtqqqn6daw3usv2emkjym4t"; //this works fine lpcwstr pszinput = l"mytest"; //doesnt work, api returns false,error code 0x0000000d // determine size of byte array , allocate memory. if(! cryptstringtobinary( pszinput, _tcslen( pszinput ) + 1, crypt_string_base64, null, &dwdatalen, &dwskip, &dwflags ) ) { dword dw = getlasterror(); //0x0000000d: data invalid throw std::exception( "error computing byte length." ); } byte *pbytebyte = null; try { ...
Read more

c++ - Correct method for redrawing a layered window -

i have window created ws_ex_layered window style. drawing onto memory bitmap using gdi+, , using updatelayeredwindow update graphical content of layered window. intend use window main window of application, require redraw frequently. seeing layered windows not receive wm_paint windows message [?] , need come appropriate method re-drawing window. optimisation not essential , it's nice have cake , eat too. therefore, in search of "correct" method use. here thoughts far: i guess it's idea render onto off-screen bitmap before bitblt ing or similar. 60 frames rendered per second should (more than?) enough (but how compare other applications' frame rates?). possible solutions: use settimer send wm_timer message on regular basis. useful because through specifying time-out value, can achieve desired frames per second, without requirement measure duration "frame" takes rendered. would cause input or other lags due frequency , speed of m...
Read more

java.util.scanner - How to read and add only numbers to array from a text file -

i'm learning java , have question regarding reading file want read numbers file contains strings well. here example of file: 66.56 "3 java 3-43 5-42 2.1 1 and here coding: public class test { public static void main (string [] args){ if (0 < args.length) { file x = new file(args[0]); try{ scanner in = new scanner( new fileinputstream(x)); arraylist<double> test = new arraylist<>(); while(in.hasnext()){ if(in.hasnextdouble()){ double f=in.nextdouble(); test.add(f);} else {in.next();} } catch(ioexception e) { system.err.println("exception during reading: " + e); } } my problem add 66.56,2.1 , 1 doesn't add 3 after "3 or ignores 3-43 , 5-42 can tell me how skip strings , add doubles here? thanks all said 3 ie; "3, 3-43 , 4-42 strings either u read string , split , check number @ " , - or put in space between characters , integers. jvm after compilation tre...
Read more