The sample_docs Example Table

The descriptions of the XML query functions reference an example table named sample_docs. This chapter shows you how to create and populate that table.

The sample_docs table has three columns and three rows.

sample_docs table columns and rows

This section shows the structure of the sample_docs table.

Sample_docs table columns

The sample_docs table has three columns:

name_doc
text_doc
image_doc

In a specified example document, name_doc specifies an identifying name, text_doc specifies the document in a text representation, and image_doc specifies the document in a parsed XML presentation stored in an image column. The following script creates the table:

create table sample_docs 
(name_doc varchar(100),   
text_doc text null,    
image_doc image null)

sample_docs table rows

The sample_docs table has three rows:

An example document, "bookstore.xml".
An XML representation of the publishers table of the pubs2 database.
An XML representation of (selected columns of) the titles table of the pubs2 database.

The following script inserts the example "bookstore.xml" document into a row of the sample_docs table:

insert into sample_docs   
    (name_doc, text_doc)   
    values ( "bookstore",

"<?xml version='1.0' standalone = 'no'?>
 <?PI_example Process Instruction ?>
 <!--example comment-->
 <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>  
 <book style='textbook'>
   <title>History of Trenton</title>
      <author>            
          <first-name>Mary</first-name>
           <last-name>Bob</last-name>
           <publication>Selected Short Stories of
           <first-name>Mary</first-name>
            <last-name>Bob</last-name>
           </publication>
      </author>
       <price>55</price>  
 </book>
 <?PI_sample Process Instruction ?>
 <!--sample comment-->
 <magazine style='glossy' frequency='monthly'>
  <title>Tracking Trenton</title>
     <price>2.50</price>
   <subscription price='24' per='year'/>
 </magazine>
 <book style='novel' id='myfave'>
     <title>Trenton Today, Trenton Tomorrow</title>
     <author>
         <first-name>Toni</first-name>
         <last-name>Bob</last-name>
         <degree from='Trenton U'>B.A.</degree>
          <degree from='Harvard'>Ph.D.</degree>
          <award>Pulizer</award>
          <publication>Still in Trenton</publication>
           <publication>Trenton Forever</publication>
     </author>
     <price intl='canada' exchange='0.7'>6.50</price>
     <excerpt>
     <p>It was a dark and stormy night.</p>
     <p>But then all nights in Trenton seem dark and
         stormy to someone who has gone through what
         <emph>I</emph> have.</p>
      <definition-list>
         <term>Trenton</term>
         <definition>misery</definition>
      </definition-list>
  </excerpt>
 </book>

 <book style='leather' price='29.50'
xmlns:my='http://www.placeholdernamehere.com/schema/'>
  <title>Who's Who in Trenton</title>
  <author>Robert Bob</author>
 </book>

 </bookstore>")

sample_docs tables

The other two rows of the sample_docs table are XML representations of the publishers and titles tables of the pubs2 database. The pubs2 database is an database of example tables that is described in the Transact-SQL User's Guide.

The publishers and titles tables are two of the tables in this sample database.To shorten the example, the XML representation of the titles table includes only selected columns.

The following script generates the XML representations of the publishers and titles tables with the forxmlj function, which is described in forxmlj, forxmldtdj, forxmlschemaj, forxmlallj.

Table script (for publishers table)

insert into sample_docs (name_doc, text_doc)
values ('publishers',
    forxmlj('select * from pubs2..publishers
            'tablename=publishers'))

insert into sample_docs (name_doc, text_doc)
values ('authors',    
    forxmlj('select title_id, title
    type, pub_id, price,
    advance, total_sales
    from pubs2..authors',
'tablename=authors'))

This script uses the forxmlj function, which is a Java-based function that you must install before you can use. See Appendix A, "Setting up XML Services," for instructions on installing this function.

Publishers table representation

This code sample shows the XML representation of the publishers table in the Pubs 2 database, generated by the script in sample_docs tables.

set stringsize 16384
select text_doc from sample_docs
where name_doc='publishers'

text_doc

-------------------------------------------
 <publishers
  xmlns:xsi="http://www.w3.org/2001/XMLSchema
  instance">   

 <row> 
   <pub_id>0736</pub_id>
   <pub_name>New Age Books</pub_name>
   <city>Boston</city
   <state>MA</state>   
 </row>   

 <row>      
   <pub_id>0877</pub_id>
   <pub_name>Binnet & Hardley</pub_name>
   <city>Washington</city>
   <state>DC</state>   
 </row>   

 <row>      
   <pub_id>1389</pub_id>      
   <pub_name>Algodata Infosystems</pub_name>
   <city>Berkeley</city>
   <state>CA</state>   
 </row>

 </publishers>
(1 row affected)

Titles table representation

This section shows the XML representation of selected columns of the titles table.

set stringsize 16384
select text_doc from sample_docs
where name_doc='titles' 

 text_doc
----------------------------------------------------
 <titles
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <row>
     <title_id>BU1032<title_id>
     <title>The Busy Executive's Data Base
           Guide</title>
     <type>business</type>
     <pub_id>1389</pub_id>
     <price>19.99</price>
     <advance>5000.00</advance>
     <total_sales>4095</total_sales>
  </row>

  <row>
     <title_id>BU1111</title_id>
     <title>Cooking with Computers:
               Surreptitious Balance Sheets</title>
     <type>business </type>
     <pub_id>1389</pib_id>
     <price>11.95</price>
     <advance>5000.00</advance>
     <total_sales>3876</total_sales>
  </row>

  <row>
     <title_id>BU2075</title_id>
     <title>You Can Combat Computer Stress!</title>
     <type>business </type>
     <pub_id>0736</pub_id>
     <price>2.99</price>
     <advance>10125.00</advance>
     <total_sales>18722</total_sales>
  </row>

  <row>
     <title_id>BU7832</title_id>
     <title>Straight Talk About Computers</title>
     <type>business </type>
     <pub_id>1389</pub_id>
     <price>19.99</price>
     <advance>5000.00</advance>
     <total_sales>4095</total_sales>
  </row>

  <row>
     <title_id>MC2222</title_id>
     <title>Silicon Valley Gastronomic Treats</title>
     <type>mod_cook</type>
     <pub_id>0877</pub_id>
     <price>19.99</price>
     <advance>0</advance>
     <total_sales>2032</total_sales>
  </row>

  <row>
     <title_id>MC3021</title_id>
     <title>The Gourmet Microwave</title>
     <type>mod_cook</type>
     <pub_id>0877</pub_id>
     <price>2.99</price>
     <advance>15000.00</advance>
     <total_sales>22246</total_sales>
  </row>

  <row>
     <title_id>MC3026</title_id>
     <title>The Psychology of Computer Cooking</title>
     <type>UNDECIDED</type>
     <pub_id>0877</pub_id>
  </row>

  <row>
     <title_id>PC1035</title_id>
     <title>But Is IT User Friendly?</title>
     <type>popular_comp</type>
     <pub_id>1389</pub_id>
     <price>22.99</price>
     <advance>7000.00</advance>
     <total_sales>8780</total_sales>
  </row>

  <row>
     <title_id>PC8888</title_id>
     <title>Secrets of Silicon Valley</title>
     <type>popular_comp</type>
     <pub_id>1389</pub_id>
     <price>20.00</price>
     <advance>8000.00</advance>
     <total_sales>4095</total_sales>
  </row>

  <row>
     <title_id>PC9999</title_id>
     <title>Net Etiquette</title>
     <type>popular_comp</type>
     <pub_id>1389</pub_id>
  </row>

  <row>
     <title_id>PS1372</title_id>
     <title>Computer Phobic and Non-Phobic
         Individuals: Behavior Variations</title>
     <type>psychology </type>
     <pub_id>0877</pub_id>
     <price>21.59</price>
     <advance>7000.00</advance>
     <total_sales>375</total_sales>
  </row>

  <row>
     <title_id>PS2091</title_id>
     <title>Is Anger the Enemy?</title>
     <type>psychology </type>
     <pub_id>0736</pub_id>
     <price>10.95</price>
     <advance>2275.00</advance>
     <total_sales>2045</total_sales>
  </row>

  <row>
     <title_id>PS2106</title_id>
     <title>Life Without Fear</title>
     <type>psychology </type>
     <pub_id>0736</pub_id>
     <price>7.99</price>
     <advance>6000.00</advance>
     <total_sales>111</total_sales>
  </row>

  <row>
     <title_id>PS3333</title_id>
     <title>Prolonged Data Deprivation:
           Four Case Studies</title>
     <type>psychology</type>
     <pub_id>0736</pub_id>
     <price>19.99</price>
     <advance>2000.00</advance>
     <total_sales>4072</total_sales>
  </row>

  <row>
     <title_id>PS7777</title_id>
     <title>Emotional Security:
         A New Algorithm</title>
     <type>psychology </type>
     <pub_id>0736</pub_id>
     <price>7.99</price>
     <advance>4000.00</advance>
     <total_sales>3336</total_sales>
  </row>

  <row>
     <title_id>TC3218</title_id>
     <title>Onions, Leeks, and Garlic:
        Cooking Secrets of the Mediterranean</title>
     <type>trad_cook </type>
     <pub_id>0877</pub_id>
     <price>20.95</price>
     <advance>7000.00</advance>
     <total_sales>375</total_sales>
  </row>

  <row>
     <title_id>TC4203</title_id>
     <title>Fifty Years in Buckingham
          Palace Kitchens</title>
     <type>trad_cook </type>
     <pub_id>0877</pub_id>
     <price>11.95</price>
     <advance>4000.00</advance>
     <total_sales>15096</total_sales>
  </row>

  <row>
     <title_id>TC7777</title_id>
     <title>Sushi, Anyone?</title>
     <type>trad_cook </type>
     <pub_id>0877</pub_id>
     <price>14.99</price>
     <advance>8000.00</advance>
     <total_sales>4095</total_sales>
  </row>

 </titles>

(1 row affected)


	Expand Search