Convert XML to SQL

COMP207 Lectures 2021-12-06

We can convert the other way by using XML Schema.

To convert an XML database to SQL we have the following approaches:

Storing XML in an Attribute

We can use the XML data type (XML or XMLType) to store raw XML in a serialised form.

Creating Tables Using XML Type

We can use the following SQL:

CREATE TABLE XMLStaff (
	docNo CHAR(4),
	docDate DATE,
	staffData XML
);
INSERT INTO XMLStaff VALUES (
	'D001',
	'2004-12-01',
	XML('
		<STAFF branchNo = "B005">
		<STAFFNO>SL21</STAFFNO>
		<POSITION>Manager</POSITION>
		<DOB>1945-10-01</DOB>
		<SALARY>30000</SALARY> </STAFF>
	')
);

Schema-Independent Representation

This method allows us to store the XML as a tree inside of a table:

• This creates the issue of making the tree difficult to search.
  • To solve this we can use a denormalised index in a second table that links nodes and their parents.

From this we then get the following schema-independent table:

nodeID	nodeType	nodeName	nodeData	parentID	rootID
0	Document	STAFFLIST			0
1	Element	STAFFLIST		0	0
2	Element	STAFF		1	0
3	Element	STAFFNO		2	0
4	Text		SL21	3	0
5	Element	NAME		2	0
6	Element	FNAME		5	0
7	Text		John	6	0
8	Element	LNAME		5	0
9	Text		White	8	0

and it’s denormalised index:

path	nodeID	parentID
/STAFFLIST	1	0
STAFFLIST	1	0
STAFFLIST/STAFF	2	1
STAFF	2	1
/STAFFLIST/STAFF/NAME	5	2
STAFFLIST/STAFF/NAME	5	2
STAFF/NAME	5	2
NAME	5	2

Storing XML in Shredded Form

• XML is decomposed into its constituent element and data distributed over a number of attributes in one or more relations.
• Storing shredded document may make it easier to index values of some element, provided these element are placed into their own attributes.
• Also possible to add some additional data relation to hierarchical nature of the XML, making it possible to recompose the original structure and ordering.
  • This allows the XML to be updated.

With this approach we also have to create an appropriate database structure.