OraFAQ Forum: SQL & PL/SQL » accessing xml values from a table

Home » SQL & PL/SQL » SQL & PL/SQL » accessing xml values from a table (windows 7)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

accessing xml values from a table [message #575967]

Tue, 29 January 2013 22:05

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?

Report message to a moderator

Re: accessing xml values from a table [message #575968 is a reply to message #575967]

Tue, 29 January 2013 22:12

BlackSwan
Messages: 20099
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/

Report message to a moderator

Re: accessing xml values from a table [message #575969 is a reply to message #575968]

Tue, 29 January 2013 23:17

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

Report message to a moderator

Re: accessing xml values from a table [message #575971 is a reply to message #575969]

Tue, 29 January 2013 23:24

BlackSwan
Messages: 20099
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

interesting query

please post results from SQL below

SELECT * FROM V$VERSION;

Report message to a moderator

Re: accessing xml values from a table [message #575974 is a reply to message #575971]

Wed, 30 January 2013 00:09

Littlefoot
Messages: 16971
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.

Report message to a moderator

Re: accessing xml values from a table [message #575997 is a reply to message #575969]

Wed, 30 January 2013 07:42

Solomon Yakobson
Messages: 1398
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.

Report message to a moderator

Re: accessing xml values from a table [message #575999 is a reply to message #575997]

Wed, 30 January 2013 07:49

Solomon Yakobson
Messages: 1398
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 02:41:05 CDT 2013

Total time taken to generate the page: 0.19112 seconds