Oracle XML DB – XMLType table creation

Wednesday, March 18th, 2015

Background

So I’ve been doing some Oracle XML work again. I’d lost my notes from last time (nice touch) so here are my notes from this time:

XMLType Table Creation

CREATE TABLE xml_table OF XMLType

This will create “an xml table” into which you can insert XML. It will check that the XML is well formed before insertion. It will not validate that all documents inserted are the same or that elements contain valid data.

For that we need to create a table based upon an XML Schema/XSD

-- create an Oracle directory where the schema can be stored
create or replace directory XSD as '/home/foo/bar/oracle/xsd'

-- copy your Schema into the directory
-- then
BEGIN
  DBMS_XMLSCHEMA.registerSchema(SCHEMAURL => 'http://www.foobar.com/xsd/my_schema.xsd', SCHEMADOC => bfilename('XSD','my_schema.xsd'));
END;

-- Note: the SCHEMAURL can be basically anything. Oracle uses it as a unique identifier
-- Create the table
CREATE TABLE matter OF xml_table XMLSCHEMA "http://www.foobar.com/xsd/my_schema.xsd" ELEMENT "nameOfTheRootXmlElement";

There are many overloaded versions of DBMS_XMLSCHEMA.registerSchema so you can shortcut some of the steps above. For example:

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    http://www.foobar.com/xsd/my_schema.xsd',
    bfilename('XSD','my_schema.xsd'),
    TRUE, -- generate required Oracle types. Default = true
    TRUE, -- generate javabeans. Default = false
    FALSE, -- generate errors when registering schema. True = do not
    TRUE -- generate table
);
END;

Full docs for the DBMS_XMLSCHEMA package are here
here

Tags: , , , , ,
Posted in Development, Examples, How to's | No Comments »

Java to XML, XML to Java (Marshalling and Unmarshalling)

Wednesday, July 13th, 2011

Introduction

JDK6 and JAXB2.x (which comes with JDK6) make marshalling Java to XML and unmarshalling XML to Java a snap, almost trivial.

Example

Java to XML

package foo.bar;

import java.math.BigDecimal;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.Marshaller;

public class JavaToXML {
	public static void main(String[] args) throws Exception {
		JAXBContext context = JAXBContext.newInstance(Product.class);

		Marshaller m = context.createMarshaller();
		m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);

		Product object = new Product();
		object.setCode("WI1");
		object.setName("Widget Number One");
		object.setPrice(BigDecimal.valueOf(300.00));

		m.marshal(object, System.out);
	}
}

XML to Java

package foo.bar;

import java.io.File;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;

public class XMLToJava {

	public static void main(String[] args) {
		try {
			JAXBContext jc = JAXBContext.newInstance(Product.class);
			Unmarshaller u = jc.createUnmarshaller();

			File f = new File("product.xml");
			Product product = (Product) u.unmarshal(f);

			System.out.println(product.getCode());
			System.out.println(product.getName());
			System.out.println(product.getPrice());
		} catch (JAXBException e) {
			e.printStackTrace();
		}
	}
}

product.java

Note: the @XmlRootElement is vital here!

package foo.bar;

import java.math.BigDecimal;

import javax.xml.bind.annotation.XmlRootElement;

@XmlRootElement
public class Product {
	private String code;
	private String name;
	private BigDecimal price;

	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public BigDecimal getPrice() {
		return price;
	}
	public void setPrice(BigDecimal price) {
		this.price = price;
	}
}

product.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<product>
    <code>WI1</code>
    <name>Widget Number One</name>
    <price>300.0</price>
</product>

Tags: , ,
Posted in Examples | 8 Comments »

The Processing Instruction Target Matching “[xX][mM][lL]” is Not Allowed

Friday, June 24th, 2011

Background

When parsing XML you receive the following error:

...The Processing Instruction Target Matching "[xX][mM][lL]" is Not Allowed...

Solution

The chances are you have some sort of whitespace (or control character) infront of your XML declaration:

..<?xml version="1.0" encoding="utf-8"?>

It may even be that you have more than one XML declaration in the document..!

Tags:
Posted in quick tips | No Comments »

Read a File as String with Java

Thursday, June 16th, 2011

Introduction

I’m always Googling for a way to do this. This seems to be the best “idiomatic” solution I’ve found. So without further ado…

Example

public String readFile(String path) throws IOException {
    FileInputStream stream = new FileInputStream(new File(path));
    try{
        FileChannel fc = stream.getChannel();
        MappedByteBuffer bb = fc.map(FileChannel.MapMode.READ_ONLY, 0, fc.size());
        return Charset.defaultCharset().decode(bb).toString();
    }
    finally {
        stream.close();
    }
}

Tags:
Posted in Development, How to's, quick tips | No Comments »

View Source of Oracle Trigger

Wednesday, May 11th, 2011 

select trigger_body from user_triggers where trigger_name = 'XXXXX'

Tags: ,
Posted in quick tips | No Comments »