OraFAQ Forum: JDeveloper, Java & XML » List all Tag and Field Names in XML

Home » Developer & Programmer » JDeveloper, Java & XML » List all Tag and Field Names in XML (Oracle 11g)

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

List all Tag and Field Names in XML [message #654698]

Wed, 10 August 2016 10:33

lokimisc
Messages: 101
Registered: February 2008

Senior 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

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654699 is a reply to message #654698]

Wed, 10 August 2016 12:23

Bill B
Messages: 1971
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

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654700 is a reply to message #654698]

Wed, 10 August 2016 13:32

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Explain

ordering	NULL

Why this not

ordering	order

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654701 is a reply to message #654699]

Wed, 10 August 2016 13:32

lokimisc
Messages: 101
Registered: February 2008

Senior 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

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654702 is a reply to message #654700]

Wed, 10 August 2016 13:33

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And why there are no

order	shipto

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654703 is a reply to message #654701]

Wed, 10 August 2016 13:34

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654704 is a reply to message #654699]

Wed, 10 August 2016 13:35

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

@Bill

See his previous topic.

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654705 is a reply to message #654700]

Wed, 10 August 2016 13:36

lokimisc
Messages: 101
Registered: February 2008

Senior 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

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654706 is a reply to message #654705]

Wed, 10 August 2016 14:07

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654708 is a reply to message #654704]

Wed, 10 August 2016 14:37

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Michel Cadot wrote on Wed, 10 August 2016 14:35

@Bill

See his previous topic.

Thanks Michel.

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654709 is a reply to message #654698]

Wed, 10 August 2016 17:51

Barbara Boehmer
Messages: 9105
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.

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654710 is a reply to message #654709]

Wed, 10 August 2016 23:51

lokimisc
Messages: 101
Registered: February 2008

Senior 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

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654711 is a reply to message #654710]

Thu, 11 August 2016 00:32

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: List all Tag and Field Names in XML [message #654760 is a reply to message #654710]

Thu, 11 August 2016 15:45

Barbara Boehmer
Messages: 9105
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(.)')
/

Report message to a moderator

Re: List all Tag and Field Names in XML [message #656231 is a reply to message #654760]

Thu, 29 September 2016 02:15

lokimisc
Messages: 101
Registered: February 2008

Senior Member

Sorry for responding late.
Thanks Barbara Boehmer .. Smile

Smile

, whatever solution you provided is working without any issue. It was my bad not to do changes as per your code.

Regards,
Lokesh

Report message to a moderator

Previous Topic:	IFrame PHP and JDeveloper JavaScripts
Next Topic:	Loading XML in Oracle table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 24 05:47:28 CDT 2025