This chapter describes use of XML with DB2.
Introduction
PureXML highlight permits you to store all around shaped XML archives in segments of database tables. Those segments have XML database. Information is kept in its local progressive structure by putting away XML information in XML section. The put away XML information can be gotten to and overseen by DB2 database server usefulness. The capacity of XML information in its local various leveled structure empowers effective hunt, recovery, and update of XML. To refresh an incentive in XML information, you have to utilize XQuery, SQL or mix of both.
Creating a database and table for storing XML data
Create a database by issuing the following syntax:
Syntax:
db2 create database xmldb
By default, databases use UTF-8 (UNICODE) code set. Activate the database and connect to it:
Syntax:
db2 activate db <db_name>
db2 connect to <db_name>
Example:
db2 activate db xmldb
db2 connect to xmldb
Create a well-formed XML file and create a table with data type of the column as ‘XML’. It is mandatory to pass the SQL query containing XML syntax within double quotation marks.
Syntax:
db2 “create table <schema>.<table>(col <datatype>,
col <xml datatype>)”
Example:
db2 "create table shope.books(id bigint not null
primary key, book XML)"
Insert xml values into table, well-formed XML documents are inserted into XML type column using SQL statement ‘INSERT’.
Syntax:
db2 “insert into <table_name> values(value1, value2)”
Example:
db2 "insert into shope.books values(1000, '<catalog>
<book>
<author> CrowdforGeeks</author>
<title>XML Developers Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2019-10-01</publish_date>
<description>An in-depth look at creating application
with XML</description>
</book>
</catalog>')"
Updating XML data in a table
You can update XML data in a table by using the following syntax:
Syntax:
db2 “update <table_name> set <column>=<value> where
<column>=<value>”
Example:
db2 "update shope.books set book='<catalog>
<book>
<author> CrowdforGeeks</author>
<title>XML Developers Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2019-10-01</publish_date>
<description>An in-depth XML</description>
</book>
</catalog>' where id=1000"
