Urgent ! SQL statement to query XMLdata type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Urgent ! SQL statement to query XMLdata type

Author

Topic

ocs2me
Starting Member

5 Posts

Posted - 2012-06-09 : 04:26:37

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 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."

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-09 : 07:43:09

Assuming the column is of data type XML, you can use one of the XML Xquery functions - for example:

SELECT
   Root_Cause_Analysis.query('RCA/PREVENTION')
FROM
   table1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 13:52:18

can be either of these depending on your requirement



declare @x xml

set @x='<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><RCA>
<SUB_PROBLEM_TYPE>
fgh
</SUB_PROBLEM_TYPE>
<SUB_ROOT_CAUSE>
Elaborate below
</SUB_ROOT_CAUSE>
<ELABORATION>
htrhtjjyykukjykhghjhgmnghmhmgmmuytuutt
</ELABORATION>
<PREVENTION>
Another string blah blah blah
</PREVENTION>
</RCA>'

SELECT
   @x.query('RCA/PREVENTION').value('.','varchar(100)')
   

output
-------------------------------------------------------
Prevention
-------------------------------------------------------
 To increase discussion effectiveness among teams.  Another string blah blah blah 



   SELECT t.u.value('.','varchar(100)') AS Prevention
   FROM @x.nodes('RCA/PREVENTION') t(u)


output
-------------------------------------------------------
Prevention
-------------------------------------------------------
 To increase discussion effectiveness among teams. 
 Another string blah blah blah

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ocs2me
Starting Member

5 Posts

Posted - 2012-06-09 : 23:36:45

quote:
Originally posted by sunitabeck

Assuming the column is of data type XML, you can use one of the XML Xquery functions - for example:
SELECT
Root_Cause_Analysis.query('RCA/PREVENTION')
FROM
table1

HI, sunitabeck. Thanks for your comment. I follow your syntax and i got this error. [ORA-22806: not an object or REF ]Would you mind to take a look in my code and drop some comment for me. :)

sqlxmltest = "SELECT T1.Root_Cause_Analysis.query('RCA/PREVENTION')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"

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>

ocs2me
Starting Member

5 Posts

Posted - 2012-06-09 : 23:47:18

quote:

Originally posted by visakh16

can be either of these depending on your requirement



declare @x xml

set @x='<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><RCA>
<SUB_PROBLEM_TYPE>
fgh
</SUB_PROBLEM_TYPE>
<SUB_ROOT_CAUSE>
Elaborate below
</SUB_ROOT_CAUSE>
<ELABORATION>
htrhtjjyykukjykhghjhgmnghmhmgmmuytuutt
</ELABORATION>
<PREVENTION>
Another string blah blah blah
</PREVENTION>
</RCA>'

SELECT
   @x.query('RCA/PREVENTION').value('.','varchar(100)')
   

output
-------------------------------------------------------
Prevention
-------------------------------------------------------
 To increase discussion effectiveness among teams.  Another string blah blah blah 



   SELECT t.u.value('.','varchar(100)') AS Prevention
   FROM @x.nodes('RCA/PREVENTION') t(u)


output
-------------------------------------------------------
Prevention
-------------------------------------------------------
 To increase discussion effectiveness among teams. 
 Another string blah blah blah

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi, visakh16. Thanks for your comment. I try your comment and it fail too.

What about i wish to perform like below.

<RCA>

<SUB_PROBLEM_TYPE>
Bla bla bla...............
</SUB_PROBLEM_TYPE>

<SUB_ROOT_CAUSE>
Bla bla bla...............
</SUB_ROOT_CAUSE>

<ELABORATION>
Bla bla bla...............
</ELABORATION>

<PREVENTION>
Bla bla bla...............
</PREVENTION>

</RCA>

I wish to generate in my html form like..

SUB_PROBLEM_TYPE : bla bla bla....
ELABORATION: bla bla bla...
PREVENTION: bla bla bla....

Is there any simple way to query out ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-10 : 00:43:36

quote:
Originally posted by ocs2me

quote:
Originally posted by sunitabeck

Assuming the column is of data type XML, you can use one of the XML Xquery functions - for example:
SELECT
Root_Cause_Analysis.query('RCA/PREVENTION')
FROM
table1

HI, sunitabeck. Thanks for your comment. I follow your syntax and i got this error. [ORA-22806: not an object or REF ]Would you mind to take a look in my code and drop some comment for me. :)

sqlxmltest = "SELECT T1.Root_Cause_Analysis.query('RCA/PREVENTION')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"

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>

the error message clearly suggests you're using Oracle
This is MS SQL Server forum and we dont have much expertise on Oracle
So please try your luck at www.dbforums.com or www.orafaq.com for Oracle related help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources