Home » SQL & PL/SQL » SQL & PL/SQL » accessing xml values from a table (windows 7)
icon4.gif  accessing xml values from a table [message #575967] Tue, 29 January 2013 22:05 Go to next message
thecynosure_pc
Messages: 2
Registered: January 2013
Location: Bangalore
Junior Member

i have a table which has 2 columns.1st column has userId and the other contains an xml data as a link.on clicking that link a new file opens containing the data in xml format.

<fields>
<field key="Public Email">piyush@chand.com</field>
<field key="Location">bangalore</field>
<field key="Website" />
<field key="Birthday">0001-01-01 00:00:00</field>
<field key="Gender">Male</field>
<field key="Language">English</field>
</fields>

i need to access location of a particular userId.
How can i do that?
Re: accessing xml values from a table [message #575968 is a reply to message #575967] Tue, 29 January 2013 22:12 Go to previous messageGo to next message
BlackSwan
Messages: 21940
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
icon6.gif  Re: accessing xml values from a table [message #575969 is a reply to message #575968] Tue, 29 January 2013 23:17 Go to previous messageGo to next message
thecynosure_pc
Messages: 2
Registered: January 2013
Location: Bangalore
Junior Member

got the query

SELECT b.c.value('(field[@key="Location"][1])','varchar(20)') as loc
FROM
[Telligent Evolution].dbo.cs_Profile_UserData a cross apply
a.Fields.nodes('/fields') AS b(c) where UserID=2100 Razz
Re: accessing xml values from a table [message #575971 is a reply to message #575969] Tue, 29 January 2013 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 21940
Registered: January 2009
Senior Member
thecynosure_pc wrote on Tue, 29 January 2013 21:17
got the query

SELECT b.c.value('(field[@key="Location"][1])','varchar(20)') as loc
FROM
[Telligent Evolution].dbo.cs_Profile_UserData a cross apply
a.Fields.nodes('/fields') AS b(c) where UserID=2100 Razz



interesting query

please post results from SQL below

SELECT * FROM V$VERSION;
Re: accessing xml values from a table [message #575974 is a reply to message #575971] Wed, 30 January 2013 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 18824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As Black Swan said - this doesn't look like an Oracle query. Are you, perhaps, using some other database? Because, this is OraFAQ forum, where people discuss Oracle (and MySQL, since Oracle bought it) problems.
Re: accessing xml values from a table [message #575997 is a reply to message #575969] Wed, 30 January 2013 07:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1791
Registered: January 2010
Senior Member
thecynosure_pc wrote on Wed, 30 January 2013 00:17
got the query


This is SQL Server query. In Oracle you'd do something like:

SQL> with t as (
  2             select xmltype('<fields>
  3  <field key="Public Email">piyush@chand.com</field>
  4  <field key="Location">bangalore</field>
  5  <field key="Website" />
  6  <field key="Birthday">0001-01-01 00:00:00</field>
  7  <field key="Gender">Male</field>
  8  <field key="Language">English</field>
  9  </fields>') xml_doc from dual
 10             )
 11  select  location
 12    from  t,
 13          xmltable(
 14                   '/fields/field[@key="Location"]'
 15                   passing xml_doc
 16                   columns
 17                     location varchar2(20) path '.'
 18                  )
 19  /

LOCATION
--------------------
bangalore

SQL> 


OR:

SQL> with t as (
  2             select xmltype('<fields>
  3  <field key="Public Email">piyush@chand.com</field>
  4  <field key="Location">bangalore</field>
  5  <field key="Website" />
  6  <field key="Birthday">0001-01-01 00:00:00</field>
  7  <field key="Gender">Male</field>
  8  <field key="Language">English</field>
  9  </fields>') xml_doc from dual
 10             )
 11  select  extractvalue(xml_doc,'/fields/field[@key="Location"]')
 12    from  t
 13  /

EXTRACTVALUE(XML_DOC,'/FIELDS/FIELD[@KEY="LOCATION"]')
--------------------------------------------------------------------
bangalore

SQL> 


SY.
Re: accessing xml values from a table [message #575999 is a reply to message #575997] Wed, 30 January 2013 07:49 Go to previous message
Solomon Yakobson
Messages: 1791
Registered: January 2010
Senior Member
And if you want to get multiple attributes using XMLTABLE:

SQL> with t as (
  2             select xmltype('<fields>
  3  <field key="Public Email">piyush@chand.com</field>
  4  <field key="Location">bangalore</field>
  5  <field key="Website" />
  6  <field key="Birthday">0001-01-01 00:00:00</field>
  7  <field key="Gender">Male</field>
  8  <field key="Language">English</field>
  9  </fields>') xml_doc from dual
 10             )
 11  select  location,
 12          language
 13    from  t,
 14          xmltable(
 15                   '/fields'
 16                   passing xml_doc
 17                   columns
 18                     location varchar2(20) path 'field[@key="Location"]',
 19                     language varchar2(20) path 'field[@key="Language"]'
 20                  )
 21  /

LOCATION             LANGUAGE
-------------------- --------------------
bangalore            English

SQL> 


SY.
Previous Topic: Re-Arrange Serial
Next Topic: Goto option not working in EXCEPTION
Goto Forum:
  


Current Time: Thu Apr 17 15:23:47 CDT 2014

Total time taken to generate the page: 0.09377 seconds