sql - getting name and value from xmltype attribute in oracle -

-

description

hello, have problem extracting attribute names , values xmltype value in oracle. basically, have table, let's tablea, has xmltype column, let's call tablea_config. values in tablea_config have structure this: <tableaconfig someattribute1="value1" someattribute2="value2" someattribute3="value3" />. number of attributes , names may vary , not known beforehand. need (for each row) create new xmlelement called tableaconfiglist, contains xmlelements called tableaconfig , each of has 2 attributes: name , value. now, number of tableaconfig nodes must equal number of attributes in tablea_config column, , each holds name of corresponding attribute in name attribute , value in value attribute.

example

from:

<tableaconfig someattribute1="value1" someattribute2="value2" someattribute3="value3" />

i should get:

<tableaconfiglist>     <tableaconfig name="someattribute1" value="value1"/>     <tableaconfig name="someattribute2" value="value2"/>     <tableaconfig name="someattribute3" value="value3"/> </tableaconfiglist>

what tried

i came idea create xmltable tablea_config column value , in create 2 columns, can later select. looks (it fragment of bigger query):

select xmlelement("tableaconfiglist",     (select         xmlagg(            xmlelement("tableaconfig",                 xmlattributes(                     tmp."attr_name" "name",                     tmp."attr_text" "value"                 )             )         ) xmltable('/tableaconfig/@*'             passing tablea.tablea_config             columns                "attr_name" varchar(30) path 'name()',                "attr_text" varchar(30) path 'text()'         ) tmp     ) ) dual

but get:

<tableaconfiglist>     <tableaconfig name="someattribute1"></tableaconfig>     <tableaconfig name="someattribute2"></tableaconfig>     <tableaconfig name="someattribute3"></tableaconfig> </tableaconfiglist>

there no value. however, if remove name xmlattributes shows up. from:

select xmlelement("tableaconfiglist",     (select         xmlagg(            xmlelement("tableaconfig",                 xmlattributes(                     tmp."attr_text" "value"                 )             )         ) xmltable('/tableaconfig/@*'             passing tablea.tablea_config             columns                "attr_name" varchar(30) path 'name()',                "attr_text" varchar(30) path 'text()'         ) tmp     ) ) dual

i get:

<tableaconfiglist>     <tableaconfig value="value1"></tableaconfig>     <tableaconfig value="value2"></tableaconfig>     <tableaconfig value="value3"></tableaconfig> </tableaconfiglist>

i thought maybe, reason, there can 1 attribute created way, if add new 1 hardcoding it, shows in result, this:

select xmlelement("tableaconfiglist",     (select         xmlagg(            xmlelement("tableaconfig",                 xmlattributes(                     tmp."attr_text" "value",                     'testvalue' "testattribute"                 )             )         ) xmltable('/tableaconfig/@*'             passing tablea.tablea_config             columns                "attr_name" varchar(30) path 'name()',                "attr_text" varchar(30) path 'text()'         ) tmp     ) ) dual

result:

<tableaconfiglist>     <tableaconfig value="value1" testattribute="testvalue"></tableaconfig>     <tableaconfig value="value2" testattribute="testvalue"></tableaconfig>     <tableaconfig value="value3" testattribute="testvalue"></tableaconfig> </tableaconfiglist>

putting in xmlattributes both columns , hardcoded 1 gives me name , testattribute, no value.

could tell me because miss terribly obvious, bug or doing wrong. pretty new oracle , pl/sql , appreciate help. thanks!

you there first attempt. while evaluating xpath, when inside attribute list /tableaconfig/@*, don't need text() value of attribute inside it. @ attribute level using "dot" . current node sufficient.

so try - 

select xmlelement("tableaconfiglist",     (select         xmlagg(            xmlelement("tableaconfig",                 xmlattributes(                     tmp."attr_name" "name",                     tmp."attr_text" "value"                 )             )         ) xmltable('/tableaconfig/@*'             passing tablea.tablea_config             columns                "attr_name" varchar(30) path 'name()',                "attr_text" varchar(30) path '.'         ) tmp     ) ) dual

the difference first attempt xpath of value attribute.


Comments

Post a Comment

Popular posts from this blog

sequelize.js - Sequelize group by with association includes id -

-
so when requesting group sequelize follows: return models.workingcalendar .findall({ attributes: [ 'workingcalendar.periodid', 'workingcalendar.date', 'period.name' ], include: [ { model: models.period, attributes: [] } ], where: { getsudoid: currentgetsudo.id, unitplantid: unitplantid }, group: ['workingcalendar.periodid', 'workingcalendar.date', 'period.name'], }); sequelize run query: select [workingcalendar].[id], [workingcalendar].[periodid], [workingcalendar].[date], [period].[name] [workingcalendars] [workingcalendar] left outer join [periods] [period] on [wor...
Read more

delphi - Take screenshot in webcam using VFrames in Console Application -

-
i'm doing program take picture of webcam using delphi xe2 , vframes achieve this, problem have figured out, in graphic application works fine, when use unit in console application, returns me error saying first chance exception @ $76b6b727. exception class eaccessviolation message 'access violation @ address 004a271b in module 'console.exe'. read of address 00000260'. process console.exe (3676) my unit : unit webcam; interface uses sysutils, windows, vcl.imaging.jpeg, vcl.graphics, vsample, vframes, classes; type twebcam = class private procedure newvideoframeevent(sender: tobject; width, height: integer; dataptr: pointer); public constructor create; destructor destroy; override; procedure capture_webcam(take_name: string); end; var web_image: tvideoimage; name_screen: string; implementation constructor twebcam.create; begin inherited create; end; destructor twebcam.destroy; begin inherited destroy; end; ...
Read more

swift - Cannot convert return expression type to return type with closure? -

-
hello have func pagination messages. class func listmessages() -> (int, int, ([chatitemprotocol]) -> void) { let service = messageservice() func list(count: int, offset: int, comp:([chatitemprotocol]) -> void) { let params : [string : anyobject] = ["offset" : offset, "limit" : count] service.listmessagesforroom(params) { (messages) in comp(messages.map({$0})) } } return list } and have error : cannot convert return expression of type '(int, offset: int, comp: ([chatitemprotocol]) -> void) -> ()' return type '(int, int, ([chatitemprotocol]) -> void)' (aka '(int, int, array<chatitemprotocol> -> ())') listmessages(...) expects following tuple return type (int, int, ([chatitemprotocol]) -> void) the function list(...) , on other hand, uses above argument implicitly contain void (/empty tuple type () ) return type. i.e., full signature lis...
Read more