sql - How to get dataset from XMLType in Oracle -

-

how 1 extract dataset set of xml documents stored in rows, sql select statement? here sample task illustrates question.

input

create table circle   ( id       number         not null primary key   , name_xml varchar2(2000)    ) /  insert circle  select 1, '<t><person><firstn>sean       </firstn> <lastn>durkin     </lastn></person>' ||               '<person><firstn>tom        </firstn> <lastn>sawyr      </lastn></person></t>' dual union  select 2, '<t><person><firstn>philip     </firstn> <lastn>marlowe    </lastn></person>' ||               '<person><firstn>john       </firstn> <lastn>wayne      </lastn></person>' ||               '<person><firstn>constantine</firstn> <lastn>palaeologus</lastn></person></t>' dual union  select 3, null dual;

so in table circle, have 5 people spread across 3 table rows. each person identified first name (firstn) , last name (lastn).

input structure

the name_xml column either empty or xml documetn root element <t>. under <t> number of <person>. , under <person> both <firstn> , <lastn> in order. spaces shown in listing readability , not in real data.

expected output

we want list of full names. should single string column. above data expect output ...

people -----------------------  sean durkin tom sawyr philip marlowe john wayne constantine palaeologus

environment

my database engine oracle database 10g enterprise edition release 10.2.0.4.0 - 64bi.

what have tried far

from have read , understood, query should work ...

select extract( name_parts, '/person/firstn') || ' ' ||         extract( name_parts, '/person/firstl') people  (  select   extract( xmltype( name_xml), '/t/person').getstringval() name_parts   circle   name_xml not null)

but returns error inconsistent data type.

11:28:27 system@dwal> l   1  select   2   trim(extractvalue( value(t), '/person/firstn')) ||' '||   3   trim(extractvalue( value(t), '/person/lastn')) people   4  circle   5  ,table(xmlsequence(extract(xmltype(name_xml), '/t/person'))) t   6* name_xml not null 11:28:28 system@dwal> /  people ---------------------------------------- sean durkin tom sawyr philip marlowe john wayne constantine palaeologus  elapsed: 00:00:00.01

or simplier using xmltable

11:36:47 system> l   1  select   2    t.fname, t.lname   3   circle   4   ,xmltable('/t/person'   5    passing xmltype(circle.name_xml)   6    columns   7     fname varchar2(20) path '/person/firstn',   8     lname varchar2(20) path '/person/lastn'   9  ) t  10*  name_xml not null 11:36:56 system> /  fname                lname -------------------- -------------------- sean                 durkin tom                  sawyr philip               marlowe john                 wayne constantine          palaeologus  elapsed: 00:00:00.12 11:36:58 system> @ver  banner ---------------------------------------------------------------- oracle database 10g enterprise edition release 10.2.0.3.0 - 64bi pl/sql release 10.2.0.3.0 - production core    10.2.0.3.0      production tns solaris: version 10.2.0.3.0 - production nlsrtl version 10.2.0.3.0 - production  elapsed: 00:00:00.01

Comments

Post a Comment

Popular posts from this blog

javascript - DIV "hiding" when changing dropdown value -

-
this related previous question asked: hide / show multiple divs i have code in place previous question , seems work ok apart when change value in dropdown "after" ticket selection made. i have number of javascrpts in place wondering if there clash somewhere? first bit of code in head of document. <head> <script type="text/javascript"> $(function() { $('.cat_dropdown').change(function() { $('#paymethod').toggle($(this).val() >= 2); }); }); </script> <script type="text/javascript"> $(document).ready(function () { $(".paymentmethod").click(function () { $(".paymentinfo").hide(); switch ($(this).val()) { case "credit card authorisation": $("#pay0").show("slow"); break; ...
Read more

Does Firefox offer AppleScript support to get URL of windows? -

-
i trying write applescript that, among other things, gets url of every open webpage in firefox. in safari (and chrome), done simply: tell application "safari" return url of every tab in every window however, seems me firefox offers no real applescript support, such getting url of tab or window. when google terms "firefox" , "applescript" together, firefox bug requests asking applescript support restored, last updated in 2010 or 2011 (like this , this ). am right in thinking, then, firefox no longer offers proper applescript support? realize there semi-workarounds, such simulating key-commands in applescript, aren't practical purposes. to see of applescript commands firefox responds to, launch applescript editor, select menu file > open dictionary... , , choose firefox application. you'll find you're expecting: firefox doesn't offer useful applescript commands.
Read more

android - How to install packaged app on Firefox for mobile? -

-
how test-install packaged (zip) app on fennec? device: physical android phone or android emulator, don't care. install mozilla-apk-cli using npm: npm install -g mozilla-apk-cli use generate "debuggable" apk app either source directory or url mini-manifest: mozilla-apk-cli /path/to/source/dir/ arbitrary-name.apk mozilla-apk-cli http://example.com/path/to/mini/manifest.webapp arbitrary-name.apk (context-click > inspect element on "free" button in marketplace discover mini-manifest url app in marketplace.) install apk on android device: adb install -r arbitrary-name.apk launch app on device. notification area notification port remote debugger server listening on. forward port on desktop, f.e. if port 12345: adb forward tcp:12345 tcp:12345 go web developer > connect… in firefox on desktop , connect localhost @ forwarded port. commence debugging! notes: use nightly builds of fennec best experience. bug 929382 tracks ...
Read more