June 9, 2012 at 3:39 am
Hi,guys. Thanks first for viewing. Hope to get help from here.Im new to SQL and XML.
Below is the data stored in database table T1.Submission_Record under field [Root_Cause_Analysis]
<RCA>
<SUB_PROBLEM_TYPE>
MRD
</SUB_PROBLEM_TYPE>
<SUB_ROOT_CAUSE>
Elaborate below
</SUB_ROOT_CAUSE>
<ELABORATION>
The volume and keypad related features
in multi-ch behavior has been
discussed for very long time. Recently,
we received the final decision from
marketing.
</ELABORATION>
<PREVENTION>
To increase discussion effectiveness
among teams.
</PREVENTION>
</RCA>
My problem is how to generate the select statement to select all the data inside the tag?
Ex: Select Root_cause_analysis(RCA/PREVENTION) From table1....
and the result should show "To increase discussion effectiveness
among teams."
June 9, 2012 at 3:46 pm
SELECT Root_Cause_Analysis.value('(/RCA/PREVENTION/text())[1]', 'varchar(64)')
FROM T1.Submission;
Eddie Wuerch
MCM: SQL
June 9, 2012 at 9:09 pm
Hi, Eddie. Thanks a lots for your comment. I follow your syntax and it give me an error : "Microsoft OLE DB Provider for Oracle error '80004005' ORA-22806: not an object or REF"
Below is my select statement
sqlxmltest = "SELECT T1.root_cause_analysis.value('(/RCA/PREVENTION/text())[1]', 'varchar(64)') as RCAXML from Submission_Record T1,feature T28,project T25 where T1.feature_identifier = T28.dbid and T1.proj_release_captured = T25.dbid and (T1.dbid <> 0 and ((T25.identifier like '"&PROJECT&"' and T1.request_scope = 'Software' and T28.dbid LIKE '"&FEATURENAME&"' ))) " & SR_STATUS & SR_STATUS2 & SR_STATUS3 & " order by T1.date_discovered ASC"
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
set rx=server.createobject("adodb.recordset")
rx.open sqlxmltest,con,1,3
while not rx.eof
<table>
<tr>
<td ><% response.write rx("RCAXML")%></td>
</tr>
</table>
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Hope to see you soon. thanks 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply