asked on

xml data

this is a continuation from the below question..

https://www.experts-exchange.com/questions/27686831/Selecting-XML-data-resulting-no-records.html

[live link removed by Zone Advisor. Sample file uploaded below]

the following fields
timestamp,status, resultsLimit should repeat for all the headline items

the xml has headline tag.. within headline tag there is "headlineItem" tag repeating 4 times..

with in headlineitem, i need to extract following fields

headline
lastmodified
links (in links i need both web and mobile url)
id
description

please let me know how to do this..

so far i am able to extract only the first 3 static fields (thanks to sdstuber) by doing as follows:

SELECT
*
FROM XMLTABLE(
'/response'
passing xmltype( bfilename('TEST_DIR','espn.xml'), nls_charset_id('AL32UTF8') )
COLUMNS id for ordinality,
time_stamp
TIMESTAMP WITH TIME ZONE PATH 'timestamp',
status
VARCHAR2(10) PATH 'status',
resultslimit
NUMBER PATH 'resultsLimit'
) x1

i have defined a directory called TEST_DIR..

Thanks

thanks

slightwv (䄆 Netminder)

Can you show 'repeating' rows and expected results?

I would also suggest you paste the XML into the question and remove the link that I assume shows your employer?

gs79

ASKER

Hi slightwv..

Thank you for your response..

I have attached the file here..may be the link become obsolete...

[original file deleted by Zone Advisor scrubbed file added below]

These are the following fields i need to extract:

1. timestamp,
2. status,
3. resultsLimit
4. headline
5. lastmodified
6. links (in links i need both web and mobile url)
a. web_link
b. mobile_link
7. id
8. description

the fields 1 to 3 will have just one value and will be repeating for all the records..

Please let me know if you need any other information..

Thanks

slightwv (䄆 Netminder)

>>I have attached the file here..may be the link become obsolete...

The link worked. I was just suggesting that it that is your employer, you might not want to link directly to your employers site.

They might not want their stuff posted in an online Q&A site. It might be considered a data leak and can affect your job.

Same for the file you just posted. You might want me to delete that and allow you to upload scrubbed data.

Let me know.

>>Please let me know if you need any other information..

I would like actual expected results. So I can see what you are wanting not have it described to me.

gs79

ASKER

well its not the company I work for..We are just trying to load the xml files we recieve..

You can delete the link. I couldnt edit the question myself to delete the link..

Thanks

slightwv (䄆 Netminder)

As long as you are OK with the contents being available on a public website I'm OK with it. I just wanted to question it since some people forget this is 'public' and post sensitive information.

Let me know if you want me to delete them or not.

gs79

ASKER

sample.xml

gs79

ASKER

you can delete the old file keep the new one that I attached

gs79

ASKER

Meanwhile I will scrub some of the data and resend it..until the attachment can be used to answer what I am looking for..

Thanks

slightwv (䄆 Netminder)

I'm still waiting for actual expected results. Not a description of the results.

gs79

ASKER

Attached here is the expected result..The query will give me 5 records for this file..So there will be one record per "headlinesItem" tag. I have expanded the last two "headlinesItem" and mentioned the values for the fields that I want with in this tag..

As you can see the first 3 fields will be repeatative,,and the rest of the fields in a record corresponds to each "headlineItem"

I do not need any other information and also information under "categories" within the "HeadlineItem" node..

Thanks
expectedresult.txt

gs79

ASKER

Please let me know if this provided the information about the expected results..Please let me know if you need any other information..

Thanks

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gs79

ASKER

Thanks everyone..

The slightwv's metod using xquery works great.

Is there any advantage of parsing xml via plsql using dbms_xmldom method VS the above method using XQuery? I have been asked to explore that and I found that using dbms_xmldom is a old way of doing..

Also another situation is, we do not have physical xml files..We are just getting the URL's..

Now is there a way to read the data in XML file behind the URL or do we have to stage the XML behind the URL somewhere in the table/or file before reading the data into relational table? How can we stage the XML's behind the URLs..

Is there a way to do it..please let me know I can open another thread..

Thanks

slightwv (䄆 Netminder)

The DOM can be less efficient in some situations. The only way to tell is to test/compare them individually.

For what you posted, my opinion is the select above will out perform the DOM.

As for the URL, did you not see sdstuber's post above: http://#a37895720

PASSING httpuritype('http://your.url.goes.here').getxml()

gs79

ASKER

Thank you very much..

I am trying to pass the url using above method. I think '&' character is causing problems..the query is promting to enter a value for

&limit
&_accept

passing httpuritype('http://api.dummy.com/v1/sports/football/nfl/teams/25/news?apikey=9nrqbfsah2m6sprwtntth3kv&limit=5&_accept=text/xml').getxml()

thanks

slightwv (䄆 Netminder)

Using sqlplus: set define off

gs79

ASKER

Excellent! Thanks everyone