Appendix C: XML Services and External File System Access

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

View this book as PDF