Home » Developer & Programmer » JDeveloper, Java & XML » List all Tag and Field Names in XML (Oracle 11g)
List all Tag and Field Names in XML [message #654698] Wed, 10 August 2016 10:33 Go to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Hi,
I'm working on a program which reads xml files and list all tags and their corresponding child fields which will be used to compare against the table and columns created in database.
Could anyone please help in listing tags and field names from xml file.
Input XML File:
<ordering>
  <order>
    <orderNumber>316</orderNumber>
    <OrderName>MOBILE</OrderName>
    <BusinessType>COMMERCIAL</BusinessType>
    <OrderStatus>PENDING</OrderStatus>
  <shipto>
    <name>Ola Nordmann</name>
    <address>Langgt 23</address>
    <city>4000 Stavanger</city>
    <country>Norway</country>
  </shipto>
  <item>
    <title>Empire Burlesque</title>
    <note>Special Edition</note>
    <quantity>1</quantity>
    <price>10.90</price>
  </item>
  </order>
<ordering>

Output ( It can be inserted into table, if required )
TagName	FieldName
ordering	NULL
order	orderNumber
order	OrderName
order	BusinessType
order	OrderStatus
shipto	name
shipto	address
shipto	city
shipto	country
item	title
item	note
item	quantity
item	price

Thanks and Regards,
Lokesh
Re: List all Tag and Field Names in XML [message #654699 is a reply to message #654698] Wed, 10 August 2016 12:23 Go to previous messageGo to next message
Bill B
Messages: 1697
Registered: December 2004
Senior Member
Why not store it directly in the database see column type XMLTYPE. The oracle database supports XML directly and you can query on the row
Re: List all Tag and Field Names in XML [message #654700 is a reply to message #654698] Wed, 10 August 2016 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Explain
ordering	NULL
Why this not
ordering	order

Re: List all Tag and Field Names in XML [message #654701 is a reply to message #654699] Wed, 10 August 2016 13:32 Go to previous messageGo to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Thanks Bill for your response.
We receive xml file using which data has to be loaded into database but issue is frequently there is addition and deletion of tags and fields in xml file. So Im trying to validate file before processing.

Please let me know, if you have any questions.

Regards,
Lokesh
Re: List all Tag and Field Names in XML [message #654702 is a reply to message #654700] Wed, 10 August 2016 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And why there are no
order	shipto
Re: List all Tag and Field Names in XML [message #654703 is a reply to message #654701] Wed, 10 August 2016 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please let me know, if you have any questions.
See my questions posted at the same time than your message.

Re: List all Tag and Field Names in XML [message #654704 is a reply to message #654699] Wed, 10 August 2016 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@Bill

See his previous topic.

Re: List all Tag and Field Names in XML [message #654705 is a reply to message #654700] Wed, 10 August 2016 13:36 Go to previous messageGo to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Thanks Michel for your response.
Ordering is root node/tag which doesn't have any fields and hence no table created for it.

Regards,
Lokesh
Re: List all Tag and Field Names in XML [message #654706 is a reply to message #654705] Wed, 10 August 2016 14:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is the string always a valid XML one or not?
If not, please provide an example of not valid xml string with its desired result.

Re: List all Tag and Field Names in XML [message #654708 is a reply to message #654704] Wed, 10 August 2016 14:37 Go to previous messageGo to next message
Bill B
Messages: 1697
Registered: December 2004
Senior Member
Michel Cadot wrote on Wed, 10 August 2016 14:35

@Bill

See his previous topic.

Thanks Michel.
Re: List all Tag and Field Names in XML [message #654709 is a reply to message #654698] Wed, 10 August 2016 17:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
Your xml file data is missing a / in the ordering closing tag, so I added that. I also used a standard hierarchy of parent and child tags, so the first two lines of the result set are a little different.

-- input_file.xml with added / in ordering closing tag:
SCOTT@orcl_12.1.0.2.0> HOST TYPE c:\my_oracle_files\input_file.xml
<ordering>
<order>
<orderNumber>316</orderNumber>
<OrderName>MOBILE</OrderName>
<BusinessType>COMMERCIAL</BusinessType>
<OrderStatus>PENDING</OrderStatus>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
</order>
</ordering>

-- Oracle directory object:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE DIRECTORY xml_files AS 'c:\my_oracle_files'
  2  /

Directory created.

-- query and results:
SCOTT@orcl_12.1.0.2.0> SELECT *
  2  FROM   XMLTABLE
  3           ('//*'
  4             PASSING XMLTYPE (BFILENAME ('XML_FILES', 'INPUT_FILE.XML'), NLS_CHARSET_ID ('AL32UTF8'))
  5             COLUMNS
  6               parent_tag VARCHAR2(30) PATH '../name(.)',
  7               child_tag  VARCHAR2(30) PATH 'name(.)')
  8  /

PARENT_TAG                     CHILD_TAG
------------------------------ ------------------------------
                               ordering
ordering                       order
order                          orderNumber
order                          OrderName
order                          BusinessType
order                          OrderStatus
order                          shipto
shipto                         name
shipto                         address
shipto                         city
shipto                         country
order                          item
item                           title
item                           note
item                           quantity
item                           price

16 rows selected.
Re: List all Tag and Field Names in XML [message #654710 is a reply to message #654709] Wed, 10 August 2016 23:51 Go to previous messageGo to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Thanks Barbara Boehmer for the solution.
But when I execute the query it is returning null values in parent_name while listed all tag names in child_name column.

I copied xml data from your post and created xml file, Please let me know if I'm missing anything.

Regards,
Lokesh
Re: List all Tag and Field Names in XML [message #654711 is a reply to message #654710] Thu, 11 August 2016 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But when I execute the query it is returning null values in parent_name while listed all tag names in child_name column.
Yes this is what it does as Barbara showed it.
She gave you a starting point, try to work with it to get your desired output.

Quote:
Please let me know if I'm missing anything.
You missed to actually show us what you did.

And answer my questions.

Re: List all Tag and Field Names in XML [message #654760 is a reply to message #654710] Thu, 11 August 2016 15:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
lokimisc wrote on Wed, 10 August 2016 21:51
Thanks Barbara Boehmer for the solution.
But when I execute the query it is returning null values in parent_name while listed all tag names in child_name column.

I copied xml data from your post and created xml file, Please let me know if I'm missing anything.

Regards,
Lokesh

You must have done something different. It is difficult to guess what that might be. You mentioned parent_name and child_name, which I did not use. I used parent_tag and child_tag, so obviously you changed those and I have to assume that you changed other things. When attempting to adapt an example, it is best to run it exactly as is, then try to make one minor modification at a time, testing between each modification, so that when something fails, you know which modification caused it. If you were to copy and paste the run of the query from SQL*Plus with line numbers and results, as I did, then it should be obvious what is different.

Since you got some results, the xml file must have been readable, so it must have been valid xml and you must have had a proper directory object, so the query must have been different. Perhaps you did not copy and paste that exactly. I will provide it again below, without line numbers, so that you can copy and paste it. If that does not solve your problem, then you need to post the run of the query from SQL*Plus, as I did, so that we can see what is different. If you are using a different directory than XML_FILES, then go ahead and change that, making sure it is in upper case, but please do not try to change anything else. All it takes is changing one little thing like / or * or . or ( or ) or a column name or upper or lower case to have something fail in such a query. If you do get it to work by copying and pasting, but have something else that does not return the same results, then you can post both and we should be able to see what is different and explain why it causes different results.

SELECT *
FROM   XMLTABLE
         ('//*'
           PASSING XMLTYPE (BFILENAME ('XML_FILES', 'INPUT_FILE.XML'), NLS_CHARSET_ID ('AL32UTF8'))
           COLUMNS
             parent_tag VARCHAR2(30) PATH '../name(.)',
             child_tag  VARCHAR2(30) PATH 'name(.)')
/
Re: List all Tag and Field Names in XML [message #656231 is a reply to message #654760] Thu, 29 September 2016 02:15 Go to previous message
lokimisc
Messages: 98
Registered: February 2008
Member
Sorry for responding late.
Thanks Barbara Boehmer ..Smile, whatever solution you provided is working without any issue. It was my bad not to do changes as per your code.

Regards,
Lokesh
Previous Topic: Load XML file using xmltype
Next Topic: Dynamic splitting of column
Goto Forum:
  


Current Time: Fri Nov 24 17:51:19 CST 2017

Total time taken to generate the page: 0.02727 seconds