Appendix B The sample_docs Example Table

Appendix D Migrating Between the Java-based XQL Processor and the Native
XML Processor

XML Services in Adaptive Server Enterprise

Appendix C XML Services and External File System Access

Appendix C

XML Services and External File System Access

The Adaptive Server External File System Access feature provides access to operating system files as SQL tables. This appendix describes the use of the native XML processor with the File System Access Feature. For more detailed information, see the Adaptive Server Component Integration Services User's Guide.

When you use the File System Access feature, you create a proxy table that maps an entire directory tree from the external file system, using Adaptive Server's Component Integration Services (CIS) feature. Then you use the built-in functions of the native XML processor on the data in the proxy table to query XML documents stored in the external file system.

With External Directory Recursive Access, you can map a proxy table to a parent directory, and to all its subordinate files and subdirectories.

Getting Started

This section explains how to set up XML Services with External File System Access capabilities.

Enabling XML services and External File System Access

Enable XML Services using sp_configure:
```
sp_configure "emable xml", 1
```
Verify that the static configuration parameter enable cis is set to 1:
```
sp_configure "enable cis"
```
Enable file access using sp_configure:
```
sp_configure "enable file access", 1
```

Examples

The following examples show how you can use various XML built-ins to query XML documents in the external file system.

Setting up your XML documents and creating the proxy table

These examples use two XML documents stored in the files named bookstore.1.xml and bookstore.2.xml, that you create:

cat bookstore.1.xml

 <?xml version='1.0' standalone = 'no'?>
 <!-- bookstore.1.xml example document--!>
 <bookstore specialty='novel'>
 <book style='autobiography'>
   <title>Seven Years in Trenton</title>
    <author>      
        <first-name>Joe</first-name>
         <last-name>Bob</last-name>
         <award>Trenton Literary Review Honorable
 Mention</award>
        </author>    
        <price>12</price>
        </book>
 </bookstore>

cat bookstore.2.xml

 <?xml version='1.0' standalone = 'no'?>
 <!-- bookstore.2.xml example document--!>
 <bookstore specialty='novel'>
   <book style='compbook'>
      <title>Modern Database Management</title>
       <author>
          <first-name>Jeffrey</first-name>
          <last-name>Hoffer</last-name>
       </author>
       <price>112.00</price>
   </book>
 </bookstore>

You can reference these XML documents with File System Access, using create proxy table.

The following code sample shows the use of create proxy table. The directory pathname in the at clause must reference a file system directory that Adaptive Server can both see and search. If you add an ';R' (indicating "Recursion") extension to the end of the pathname CIS extracts file information from every directory subordinate to the pathname.

create proxy_table xmlxfsTab external directory
at "/remote/nets3/bharat/xmldocs;R"
select filename from xmlxfsTabgo filename
-------------------------------------------
bookstore.1.xml
bookstore.2.xml

(2 rows affected)

The significant columns are filename and content. The other columns contain data for access permission and so forth. The filename column holds the file name (in this example the XML document file name) and the content column holds the actual data for that file. The datatype of the content column is image.

Example: Extracting the book title from the XML documents

select filename , xmlextract("//book/title" , content)
from xmlxfsTabgo filename
-------------------------------------------------
bookstore.1.xml
 <title>Seven Years in Trenton</title>
bookstore.2.xml
 <title>Modern Database Management</title>

(2 rows affected)

Example Importing XML documents or XML query results to an Adaptive Server table

You can transfer complete XML documents or XML query results between an File Access directory structure and either a database table or another File Access directory structure. To reference a complete XML document, use the xmlextract function with the root XPath operator ("/").

select filename ,xmlcol=xmlextract("/",content) into xmldoctab
from xmlxfsTab
------------------
(2 rows affected)

In this example, the datatype of the xmlxfsTab.content column is image, and the default datatype returned by the xmlextract built-in function is text. Therefore, specify the returns image clause in the xmlextract call to return the result as an image value. The following will create a new subdirectory, XmlDir:

insert into xmlxfsTab(filename,content)
select filename = 'XmlDir/'+filename ,
    xmlextract("/",xmlcol returns image) from xmldoctab
-----------
(2 rows affected)

This code sample queries those XML documents from the new XMLDir subdirectory:

select filename , xmlextract("//book/title" , content)
from xmlxfsTab
where filename like '%XmlDir%' and filetype = 'REG'

filename  
----------------------------------
XmlDir/bookstore.1.xml
 <title>Seven Years in Trenton</title>
XmlDir/bookstore.2.xml  
 <title>Modern Database Management</title>

(2 rows affected)

Example: Storing parsed XML documents in the file system

You can parse the XML documents stored in the external file system and store the parsed result either in an Adaptive Server table or in the File Access system.

insert xmlxfsTab(filename , content)
select 'parsed'+t.filename,xmlparse(t.content) from xmlxfsTab t
-----------
(2 rows affected)

The following code sample queries the parsed documents stored in the XFS file system.

select filename , xmlextract("//book/title" , content)
from xmlxfsTab
where filename like 'parsed%'and filetype = 'REG'
filename
--------------------------------------------
parsedbookstore.1.xml   
 <title>Seven Years in Trenton</title>
parsedbookstore.2.xml   
 <title>Modern Database Management</title>

(2 rows affected)

The following code sample uses the xmlrepresentation built-in function to query only the File Access documents that are parsed XML (rather than other sorts of external files):

select filename , xmlextract("//book/title" , content)
from xmlxfsTab
where xmlrepresentation(content) = 0
filename
---------------------------------------
parsedbookstore.1.xml
 <title>Seven Years in Trenton</title>       
parsedbookstore.2.xml     
 <title>Modern Database Management</title>

(2 rows affected)

Example: 'xmlerror'option capabilities with External File Access

An external (O/S) file system may contain a variety of data formats, and may contain both valid and invalid XML documents. You can use the xmlerror option of the xmlextract and xmltest functions to specify error actions for documents that are not valid XML.For example, a File Access directory structure may contain picture.jpg and nonxmldoc.txt files along with bookstore1.xml and bookstore.2.xml files:

select filename from xmlxfsTab
filename
-----------------------------------------
picture.jpg 
bookstore.1.xml 
bookstore.2.xml
nonxmldoc.txt

(4 rows affected)

The following code sample shows an XML query on both XML and non-XML data:

select filename , xmlextract("//book/title",content)
from xmlxfsTab
--------------
Msg 14702, Level 16, State 0:
Line 1:
XMLEXTRACT(): XML parser fatal error <<An exception occurred!
Type:TranscodingException, Message:
An invalid multi-byte source text sequence was
encountered>> at line 1, offset 1.

Example: Specifying the 'xmlerror=message' option in xmlextract

In this example, we specify the 'xmlerror= message' option in the xmlextract call. This will return the XML query results for XML documents that are valid XML, and return an XML error message element for documents that are not valid XML.

select filename , xmlextract("//book/title",content
     option 'xmlerror = message') from xmlxfsTab
filename
-------------------
picture.jpg
 <xml_parse_error>An exception occurred! 
Type:TranscodingException, 
Message:An invalid multi-byte source text sequence was
encountered</xml_parse_error>

bookstore.1.xml
 <title>Seven Years in Trenton</title>

bookstore.2.xml
 <title>Modern Database Management</title>
nonxmldoc.txt
 <xml_parse_error>Invalid document structure</xml_parse_error>

(4 rows af

Example: Parsing XML and non-XML documents with the 'xmlerror=message' option

This code sample specifies the 'xmlerror= message' option in the xmlparse call. This will store the parsed XML for XML documents that are valid XML, and store a parsed XML error message element for documents that are not valid XML.

insert xmlxfsTab(filename , content)
select 'ParsedDir/'+filename , xmlparse(content option
      'xmlerror = message')
from xmlxfsTab
--------------

(4 rows affected)

The following code sample applies the xmlextract built-in function on parsed data and gets the list of non-XML data, along with exception message information.

select filename , xmlextract('/xml_parse_error', content)
from xmlxfsTab
where '/xml_parse_error' xmltest content and filename like 'ParsedDir%'

----------------
Or with xmlrepresentation builtin
select filename , xmlextract('/xml_parse_error', content)
from xmlxfsTab
where xmlrepresentation(content) =  0
and '/xml_parse_error' xmltest content
filename
----------------------------------
ParsedDir/picture.jpg
 <xml_parse_error>An exception occurred!
Type:TranscodingException, 
Message:An invalid multi-byte source text sequence was
encountered</xml_parse_error>

ParsedDir/nonxmldoc.txt

 <xml_parse_error>Invalid document structure
 </xml_parse_error>

(2 rows affected)

Example: Using the option 'xmlerror=null' for non-XML documents

The following code sample specifies the 'xmlerror = null' option with a File Access table:

select filename , xmlextract("//book/title", content
     option 'xmlerror = null') 
from xmlxfsTabgo filename
----------------------------
picture.jpg
NULL
bookstore.1.xml 
 <title>Seven Years in Trenton</title>

bookstore.2.xml
 <title>Modern Database Management</title>
nonxmldoc.txt    
NULL  

(4 rows affected)

The following code sample selects the list of non-XML documents names with 'xmlerror = null' option.

select filename from xmlxfsTab 
where '/' not xmltest content
     option 'xmlerror = null'
filename         
-----------------------------
picture.jpg
nonxmldoc.txt

(2 rows affected)

Introduction

The Java-based XQL processor and the native XML processor are similar but different. They both implement query languages and return documents in parsed form, but they use different functions and methods.

The native XML processor implements XML query language. It provides a built-in function, xmlparse, that returns, in parsed form, a document suitable for efficient processing with the xmlextract and xmltext built-in functions.
The Java-based XQL processor is an earlier facility that implements the XQL query language. It provides a Java method, com.sybase.xml.xql.Xql.parse, that returns a parsed form of a document that is a sybase.aseutils.SybXmlStream object, suitable for processing with the com.sybase.xml.xql.Xql.query method.

If you want to migrate documents between the Java-based XQL processor and the native XML processor, you should be aware of the following possibilities and restrictions:

Documents in text form can be processed directly by both the Java-based XQL processor and the native XML processor.
The sybase.aseutils.SybXmlStream documents generated by com.sybase.xml.xql.Xql.parse can only be processed by the Java-based XQL processor. They cannot be processed by the built-in functions xmlextract or xmltest.
The parsed documents generated by the xmlparse built-in function can only be processed by the xmlextract and xmltest built-in functions. They cannot be processed by the Java-based XQL processor.

Migrating documents and queries

The following sections describe techniques for migrating documents and queries between the Java-based XQL processor and the native XML processor.

Migrating documents between the Java-based XQL processor and the native XML processor

There are two approaches you can use to migrate documents between the Java-based XQL processor to the native XML processor:

You can use the text form of the documents, if it is available.
You can generate a text version of the documents from the parsed form of the documents.

Migrating text documents between the Java-based XQL processor and the native XML processor

Suppose that you have a table such as the following, in which you have stored the text form of documents in the xmlsource column:

create table xmltab (xmlsource text, xmlindexed image)

If you want to process the documents with the native XML processor, using the xmlextract and xmltest built-in functions, you can update the table as follows:

update xmltab
set xmlindexed = xmlparse(xmlsource)

If you want to process the documents with the Java-based XQL processor, using the com.sybase.xml.xql.Xql.query method, you can update the table as follows:

update xmltab
set xmlindexed
    = com.sybase.xml.xql.Xql.parse(xmlsource)

Migrating documents from regenerated copies

Suppose that you have stored only parsed forms of some documents, using either the xmlparse built-in function for the native XML processor or the com.sybase.xml.xql.Xql.parse method for the Java-based XQL processor. For example, you might have such documents in a table as the following:

create table xmltab (xmlindexed image)

If you want to regenerate the text for such documents, you can alter the table to add a text column:

alter table  xmltab add xmlsource text null

Regenerating text documents from the Java-based XQL processor

This section demonstrates regenerating the text form of the documents from the form generated for the Java-based XQL processor.

If the xmlindexed column contains sybase.aseutils.SybXmlStream data generated by com.sybase.xmlxql.Xql.parse, you can regenerate the text form of the document in the new xmlsource column with the following SQL statement:

update xmltab
set xmlsource
   = xmlextract("/xql_result/*",
      com.sybase.xml.xql.Xql.query("/",xmlindexed) )

This statement generates text form of the document in two steps:

The com.sybase.xml.xql.Xql.query call with the "/" query generates a text form of the document, enclosed in an XML tag <xql_result>...</xql_result>.
The xmlextract call with the "/xql_result/*" query removes the <xql_result>...</xql_result> tag, and returns the text form of the original document.

You can then process the xmlsource column directly with the native XML processor, using the xmlextract and xmltest built-in functions, or you can update the xmlindexed column for the native XML processor, as follows:

update xmltab
set xmlindexed = xmlparse(xmlsource)

If you don't want to add the xmlsource column, you can combine these steps, as in the following SQL statement:

update xmltab
set xmlindexed
  = xmlparse(xmlextract("/xql_result/*",
      com.sybase.xml.xql.Xql.query("/",xmlindexed) ) )

Before this update statement is executed, the xmlindexed column contains the sybase.aseutiles.SybXmlStream form of the documents, generated by the com.sybase.xml.xql.Xql.parse method. After the update statement, that column contains the parsed form of the documents, suitable for processing with the xmlextract and xmlparse methods.

Regenerating text documents from the native XML processor

This section demonstrates regenerating the text form of the documents from the form generated for the native XML processor.

If the xmlindexed column contains data generated by the xmlparse function, you can regenerate the text form of the document in the new xmlsource column with the following SQL statement:

update xmltab
set xmlsource  = xmlextract("/",  xmlindexed)

You can then

process the xmlsource column directly with the Java-based XQL processor, using com.sybase.xml.xql.Xql.query, OR
update the xmlindexed column with the parsed form suitable for processing with the Java-based XQL processor, using the following statement:
```
update xmltab
set xmlindexed
  = com.sybase.xml.xql.Xql.parse(xmlsource)
```

If you don't want to add the xmlsource column, you can combine these steps, as in the following SQL statement:

update xmltab
set xmlindexed
    = com.sybase.xml.xql.Xql.parse
      (xmlextract("/", xmlindexed))

Before this update statement is executed, the xmlindexed column contains the parsed form of the documents, generated by the xmlparse built-in function. After the update statement, that column contains the parsed form of the documents, generated by com.sybase.xml.xql.Xql.parse, suitable for processing with com.sybase.xml.xql.Xql.query.

Migrating queries between the native XML processor and the Java-based XQL processor

The XQL language implemented by the Java-based XQL processor and the XML Query language implemented by the native XML processor are both based on the XPath language. There are two primary differences between them:

Subscripts begin with "1" in the XML Query language, and with "0" in the XQL Language.
The Java-based XQL processor returns results enclosed in "<xql_result>...</xql_result>" tags, and the native XML processor does not.


	Expand Search