Store and Retrieve XML from Databases with XSU

Store and Retrieve XML from Databases with XSU

ou can think of an XML document as a relational table where the various elements represent columns in a database table. Therefore, by mapping the various elements to a table, you can store XML in relational databases. This mapping process is called XML-to-SQL mapping technology.

Oracle created the XML SQL (XSU) Java API to convert XML to SQL, and vice versa. Before you can perform such mapping, you must create the table to which you want to map the XML document. XSU maps XML elements to specified database table columns. You should be aware that mapping an XML document to a database table with XSU does not store the attributes of that XML document; but you can store those by applying an XSLT transformation to the XML document prior to storing it, transforming the attributes you want to store into elements, which you can map to columns. XSU also lets you retrieve data from the database and convert the data to an XML document. Again, XML documents generated with XSU do not include attributes; however, you can create them post-XSU by transforming the XSU-generated document with XSLT.

Author’s Note: As of XSU version 2.1.0 (included in XDK 10g) you can also map a database column to an attribute by customizing the SQL SELECT statement.

In this article you’ll see the steps required to map an example XML document with elements and attributes to an Oracle database 10g table, and to retrieve an XML document that includes attributes by mapping the database columns to an XML document. It’s worth noting that you’re not limited to using Oracle to store XML documents with XSU; you could just as easily use another relational database such as MySQL.

Preliminary Setup
To use Oracle as the database, you’ll need to install the Oracle 10g database, including the sample schemas. You’ll also need the XSU Java API classes. First, download and install XDK 10g. Add the following three files to your CLASSPATH variable, where represents your install path:

  • /lib/xsu12.jar
  • /lib/xmlparserv2.jar
  • /lib/xdb.jar

Create a database instance; I’ve used an OracleDB database instance in this article. To establish a JDBC connection with a database, you need an appropriate JDBC driver; for Oracle 10g, add these three files to your CLASSPATH variable, where is the directory in which you installed Oracle 10g:

  • /jdbc/lib/ojdbc14.jar
  • /LIB/servlet.jar
  • /jdbc/lib/classes12dms.jar

The examples in this article use the XML document catalog.xml, shown in Listing 1 (also available in the downloadable code).

You’ll need to create a database table that includes columns for each element tag and attribute in the example XML document.

You’ll need to create a database table that includes columns for each element tag and attribute in the example XML document. Here’s an SQL script you can use to create a database table named JOURNAL that has the appropriate columns:

   CREATE  TABLE OE.JOURNAL (      JOURNAL_TITLE VARCHAR(255),       PUBLISHER VARCHAR(255),       EDITION VARCHAR(255),       ARTICLE_SECTION VARCHAR(255),       TITLE VARCHAR(255),       AUTHOR VARCHAR(255)   );

XML Document to SQL Database Mapping
If you were to store the XML document in Listing 1 directly with XSU, only the element tags in the XML document would get stored. But you can store the attributes in the XML document by first converting the attributes to elements and storing the converted XML document rather than the original. You can convert the attributes to elements by applying an XSLT stylesheet to the XML document.

You store an XML document in the database using the OracleXMLSave class. Here’s the procedure. First, import the OracleXMLSave class:

   import oracle.xml.sql.dml.OracleXMLSave;

Next, create a database connection:

   DriverManager.registerDriver(new       oracle.jdbc.driver.OracleDriver());      Connection conn =  DriverManager.getConnection(      "jdbc:oracle:thin:@::,"      ",""");

The preceding code has several placeholders, delimited in brackets, for which you must substitute your own information:

  • is the database host.
  • is the database port.
  • is the database SID.
  • is the username to login to the database.
  • is the password to login to the database.

You can get the appropriate values for , , and from your /NETWORK/ADMIN/tnsnames.ora file.

You need to convert the attributes in the example XML document to element tags by applying an XSLT to the example XML document.

Create an instance of the OracleXMLSave class:

   OracleXMLSave oracleXMLSave = new       OracleXMLSave(conn, "JOURNAL");

In the preceding line, “JOURNAL” is the name of the database table you created during the preliminary setup.

You need to convert the attributes in the example XML document to element tags by applying an XSLT to the example XML document. First, assign an XSLT stylesheet to the OracleXMLSave object. Doing this causes the save process to transform the input XML document before saving it:

   Reader xsltReader=new FileReader(      new File("c:/catalog/input.xslt"));   oracleXMLSave.setXSLT(xsltReader, null);

The XSLT used to convert the attributes in the example XML document to elements is listed in Listing 2.

Applying the stylesheet results in the altered XML format shown below. Notice that the attributes of the original catalog.xml document have been transformed into elements:

                  Oracle Magazine       Oracle Publishing       Sept-Oct 2003       XML       Parsing XML Efficiently       Julie Basu              Oracle Magazine      Oracle Publishing      Nov-Dec 2003      SQL      The Active Database       Cameron ORourke        

Set the tag for the row-enclosing element. Setting the row tag to ‘null’ sets the top level element tags to correspond to the database rows. In this case, set the row tag to journal, so that each journal tag in the example XML document will correspond to a database row:

   oracleXMLSave.setRowTag("journal");

Finally, convert the example XML document to a database table, using this code:

   oracleXMLSave.insertXML(oracleXMLSave.getURL(      "file://c:/catalog/catalog.xml"));

In this case, the XSLT file input.xslt gets applied to the example XML document, then the document gets stored in the database.

SQL Database to XML Document Mapping
To transform data in the other direction?from database tables to XML?you use the OracleXMLQuery class.

XSU creates an XML element corresponding to each database table column, but doesn’t create element attributes. Again, to create the element attributes, you apply an XSLT to the XML document created from the database table.

Here’s the procedure. First, import the OracleXMLQuery class:

   import oracle.xml.sql.query.OracleXMLQuery;

Next, create an OracleXMLQuery object instance.

   OracleXMLQuery query = new OracleXMLQuery(      conn, "SELECT JOURNAL_TITLE, PUBLISHER, EDITION, " +       "ARTICLE_SECTION, TITLE, AUTHOR FROM JOURNAL");

In the preceding code, conn is the JDBC connection used to query the database.

Set the XSLT stylesheet that you want the OracleXMLQuery object to use to generate the final XML document, including element attributes:

   Reader xsltReader=new FileReader(      new File("c:/catalog/output.xslt"));   query.setXSLT(xsltReader, null);

You can see the XSLT output.xslt in Listing 3.

Set the row tag to the element tag in the generated XML document that should correspond to a database table row.

   query.setRowTag("journal");

Finally, generate the XML document from the JOURNAL database table:

   String xmlString=query.getXMLString();

As with the previous example, the OracleXMLQuery instance applies the output.xslt stylesheet set earlier to the XML document generated with XSU, generating (recreating) the XML document shown in Listing 1. An XML document with attributes could also be generated by modifying the SQL SELECT statement, instead of applying an XSLT, but the XML document generated would consist of the root element ROWSET, instead of catalog, and row element ROW instead of journal. The SQL statement that would create attributes for JOURNAL._TITLE, PUBLISHER, EDITION, and ARTICLE_SECTION columns is as follows:

   SELECT JOURNAL_TITLE "@title", PUBLISHER "@publisher",       EDITION "@edition",  ARTICLE_SECTION "@section",       TITLE, AUTHOR FROM JOURNAL

In other words, taken together, the two processes you’ve seen here stored an XML document in a database, and then retrieved a copy of the original document. In the downloadable code for this project you’ll find the XMLToDatabase.java program, and the sample XML file.

So, you’ve seen a workaround process that uses XSLT stylesheet to let you store and retrieve XML element attributes. While this article focused only on XSU’s native ability to store and retrieve documents from a single database table, if you’re willing to do a little extra work, you can break up the XML into chunks with single-table affinity and insert the content that way. You can also use updatable views. See Chapter 9, “Using the XML SQL Utility (XSU)” of the online Oracle XML Developer’s Kit Programmer’s Guide 10g Release 2 (10.2) for additional XSU constraints and solutions.

devx-admin

devx-admin

Share the Post:
Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security.

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private,

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists

AI Tool

Unleashing AI Power with Microsoft 365 Copilot

Microsoft has recently unveiled the initial list of Australian clients who will benefit from Microsoft 365 (M365) Copilot through the exclusive invitation-only global Early Access Program. Prominent organizations participating in

Microsoft Egnyte Collaboration

Microsoft and Egnyte Collaboration

Microsoft has revealed a collaboration with Egnyte, a prominent platform for content cooperation and governance, with the goal of improving real-time collaboration features within Microsoft 365 and Microsoft Teams. This

Best Laptops

Top Programming Laptops of 2023

In 2023, many developers prioritize finding the best laptop for programming, whether at home, in the workplace, or on the go. A high-performing, portable, and user-friendly laptop could significantly influence

Renaissance Gaming Magic

AI Unleashes A Gaming Renaissance

In recent times, artificial intelligence has achieved remarkable progress, with resources like ChatGPT becoming more sophisticated and readily available. Pietro Schirano, the design lead at Brex, has explored the capabilities

New Apple Watch

The New Apple Watch Ultra 2 is Awesome

Apple is making waves in the smartwatch market with the introduction of the highly anticipated Apple Watch Ultra 2. This revolutionary device promises exceptional performance, robust design, and a myriad

Truth Unveiling

Unveiling Truths in Bowen’s SMR Controversy

Tony Wood from the Grattan Institute has voiced his concerns over Climate and Energy Minister Chris Bowen’s critique of the Coalition’s support for small modular nuclear reactors (SMRs). Wood points

Avoiding Crisis

Racing to Defy Looming Financial Crisis

Chinese property developer Country Garden is facing a liquidity challenge as it approaches a deadline to pay $15 million in interest associated with an offshore bond. With a 30-day grace

Open-Source Development

Open-Source Software Development is King

The increasingly digital world has led to the emergence of open-source software as a critical factor in modern software development, with more than 70% of the infrastructure, products, and services

Home Savings

Sensational Savings on Smart Home Security

For a limited time only, Amazon is offering massive discounts on a variety of intelligent home devices, including products from its Ring security range. Running until October 2 or while

Apple Unleashed

A Deep Dive into the iPhone 15 Pro Max

Apple recently unveiled its groundbreaking iPhone 15 Pro and iPhone 15 Pro Max models, featuring a revolutionary design, extraordinary display technology, and unrivaled performance. These new models are the first

Renewable Crypto Miners

Crypto Miners Embrace Renewable Energy?

As the cryptocurrency sector deals with the fallout from the FTX and Celsius exchange collapses, Bitcoin miners are increasingly exploring alternative energy sources to reduce expenses and maintain profitability. Specialists

Laptop Savings

The HP Omen 16 is a Gamer’s Dream

Best Buy is currently offering an unbeatable deal on the HP Omen 16 gaming laptop, giving potential buyers the chance to save a significant $720 on their purchase. Originally priced

How to Check for Vulnerabilities in Exchange Server

It is imperative to keep your systems and infrastructure up-to-date to mitigate security issues and loopholes, and to protect them against any known vulnerabilities and security risks. There are many