Hello, everyone. This is kind of a newbie question on XML queries. I have learned enough to put together a query like this:
SQL> SELECT EXTRACTVALUE (COLUMN_VALUE, 'Account/Name') acct_name
2 , EXTRACTVALUE (COLUMN_VALUE, 'Account/Total_Hours') acct_hours
3 FROM TABLE
4 (XMLSEQUENCE
5 (EXTRACT
6 (XMLTYPE
7 (
8 '<Oracle_Extract>
9 <Emp_Num>05417</Emp_Num>
10 <Emp_Name>Fitzgerald</Emp_Name>
11 <Year>2007</Year>
12 <Month>October</Month>
13 <Vehicle>1330</Vehicle>
14 <Accounts>
15 <Account>
16 <Name>184.62</Name>
17 <Total_Hours>12</Total_Hours>
18 <Percent>6</Percent>
19 <Daily_Costing>
20 <Day>26</Day>
21 <Hours>4</Hours>
22 <Day>30</Day>
23 <Hours>8</Hours>
24 </Daily_Costing>
25 </Account>
26 <Account>
27 <Name>920.06</Name>
28 <Total_Hours>49</Total_Hours>
29 <Percent>27</Percent>
30 <Daily_Costing>
31 <Day>1</Day>
32 <Hours>7</Hours>
33 <Day>2</Day>
34 <Hours>6</Hours>
35 <Day>3</Day>
36 <Hours>1</Hours>
37 </Daily_Costing>
38 </Account>
39 </Accounts>
40 <Absences>
41 </Absences>
42 </Oracle_Extract>'
43 ),
44 '/Oracle_Extract/Accounts/Account'
45 )
46 )
47 ) t
48 /
ACCT_NAME ACCT_HOURS
------------------------- --------------------------------------------------------------------------------
184.62 12
920.06 49
but what I would like to do is query this from a clob column. My table looks like this:
SQL> desc lac.lac_costing_upload
Name Type Nullable Default Comments
----------------- ------------ -------- ------- --------
UPLOAD_ID NUMBER Y
EMP_NUM VARCHAR2(20)
UPLOAD_DATE DATE Y
CREATED_BY NUMBER Y
CREATION_DATE DATE Y
LAST_UPDATED_BY NUMBER Y
LAST_UPDATE_DATE DATE Y
LAST_UPDATE_LOGIN NUMBER Y
YEAR_MONTH VARCHAR2(10) Y
UPLOAD_TEXT CLOB Y
Ideally, I could get everything in a single query:
select emp_num, year_month, extractvalue....acct_name, ....acct_hours... from lac.lac_costing_upload.
Could anyone help me with syntax, or direct me to some good learning materials?
Thanks,
--Dave