Metaproperties


Metaproperties

Anytime the term meta is used, you are usually talking about something that describes something else. If you have experience with the Java programming language and Java Database Connectivity (JDBC), the term metadata should be quite familiar to you. Metadata is data about data. For example, the title, subject, author, and size of a file constitute metadata about the file. Metaproperties associated with OPENXML, then, are attributes that describe the properties of an XML item, regardless of whether it is an element, an attribute, or any other node. These properties exist and are provided by OPENXML even though they are not present in the XML document text.The ColPattern parameter is used to map these metaproperties to the columns of a rowset created by an OPENXML statement. An edge table result has a column for each metaproperty attribute (excluding xmltext ). XPath cannot be used to access any of these properties. Listing 8.10 shows an example of a metaproperty.

Listing 8.10 A Sample Metaproperty
 ...  SELECT *  FROM OPENXML (@idoc, '/ROOT/Employee/Order')        WITH (id              int           @mp:id,              OrderID         char(5),              OrderDate       datetime,  ParentID        int '@mp:parentid'  ,  ... 

The @mp:parentid attribute provides an identifier for the ParentID element that is document wide.

To get at these metaproperties, a namespace specific to SQL Server 2000 is required. That namespace is urn:schemas-microsoft-com:xml-metaprop . Table 8.2 lists and describes the metaproperty attributes possessed by each XML element. Table 8.3 describes available parent properties that provide hierarchy information. The section following these tables will contain several examples of usage.

Table 8.2. Metaproperty Attributes

Attribute

Description

@mp:id

Provides a system-generated , document-wide identifier of the node for elements, attributes, and so on. It is guaranteed to refer to the same XML node as long as the document is not reparsed. An XML ID of 0 indicates that the element is a root element. Its parent XML ID is NULL. (Amazing, isn't it?)

@mp: localname

Stores the local part of the name of the node. It is used with prefix and namespace uniform resource identifiers (URIs) to name element or attribute nodes.

@mp:namespaceuri

Provides the current element's namespace URI.A value of NULL translates to no namespace present.

@mp:prefix

Stores the namespace prefix of the current element.

A NULL value being present with a URI being given indicates the default namespace. If no URI is given, no namespace is attached.

@mp:prev

Stores a node's previous sibling to provide element ordering information of the document.

@mp:prev contains the XML ID of the previous sibling that has the same parent element.A NULL value indicates that an element is at the beginning of the sibling list.

@mp:xmltext

This is used solely for processing purposes. It is the textual serial-ization of the element and its attributes and subelements as used in the overflow handling of OPENXML.

Table 8.3. Parent Attributes

Parent Metaproperty Attribute

Description

@mp:parentid

Corresponds to ../ @mp:id

@mp:parentlocalname

Corresponds to ../ @mp:localname

@mp:parentnamespacerui

Corresponds to ../ @mp:namespaceuri

@mp:parentprefix

Corresponds to ../ @mp:prefix

Examples of Metaproperties Usage

First let's see how the ColPattern parameter can map the metaproperties to rowset columns in an OPENXML statement. Listing 8.11 illustrates the use of several metaproperties.

Listing 8.11 Stored Procedure with Selected Metaproperties
 DECLARE @idoc int  DECLARE @xmldoc varchar(1000)  SET @xmldoc ='  <ROOT>  <Employee EmployeeID="5">     <LastName>Buchanan</LastName>     <FirstName>Steven</FirstName>     <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"            OrderDate="1996-07-04T00:00:00">        <OrderDetail ProductID="42" Quantity="10"/>        <OrderDetail ProductID="72" Quantity="5"/>Customer returned merchandise     </Order>  </Employee>  <Employee EmployeeID="7">     <LastName>King</LastName>     <FirstName>Robert</FirstName>     <Order OrderID="10303" CustomerID="GODOS" EmployeeID="7"            OrderDate="1996-09-11T00:00:00" note="Almost out of inventory">        <Priority>First Class</Priority>        <OrderDetail ProductID="40" Quantity="40"/>Customer needs this ASAP     </Order>  </Employee>  </ROOT>'  EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc  SELECT *  FROM OPENXML (@idoc, '/ROOT/Employee/Order')        WITH (id              int           @mp:id,              OrderID         char(5),              OrderDate       datetime,              ParentID        int '@mp:parentid',              ParentLocalName varchar(20) '@mp:parentlocalname')  EXEC sp_xml_removedocument @idoc 

The following example shows the results of utilizing selected metaproperties in Listing 8.11.

 id          OrderID OrderDate                    ParentID    ParentLocalName  ----------- ------- ---------------------------- ----------- -------------------- 6           10248   1996-07-04 00:00:00.000      2           Employee  22          10303   1996-09-11 00:00:00.000      18          Employee 

The metaproperties in this example are as follows :

  • @mp:id , which produces a column of guaranteed unique IDs generated by the system

  • @mp:parentid , which produces a column containing the XML ID of the column's parent

  • @mp:parentlocalname , which produces a column containing the parent element's local name

In our next example, we're going to go back again to the unconsumed (overflow) data column that we talked about in the section "Retrieving Unconsumed Data with sql:overflow-field " in Chapter 7, "FOR XML." Utilizing the @mp:xmltext metaproperty, which maps a column as an overflow column in the rowset, it is possible to retrieve the entire XML document. Listing 8.12 is the stored procedure that utilizes the @mp:xmltext metaproperty . I'll leave the results listing as an exercise for you to complete.

Listing 8.12 Using the @mp:xmltext Metaproperty
 DECLARE @idoc int  DECLARE @xmldoc varchar(1000)  SET @xmldoc ='  <ROOT>  <Employee EmployeeID="5">     <LastName>Buchanan</LastName>     <FirstName>Steven</FirstName>     <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"            OrderDate="1996-07-04T00:00:00">        <OrderDetail ProductID="42" Quantity="10"/>        <OrderDetail ProductID="72" Quantity=="5"/>Customer returned merchandise     </Order>  </Employee>  <Employee EmployeeID="7">     <LastName>King</LastName>     <FirstName>Robert</FirstName>     <Order OrderID="10303" CustomerID="GODOS" EmployeeID="7"            OrderDate="1996-09-11T00:00:00" note="Almost out of inventory">        <Priority>First Class</Priority>        <OrderDetail ProductID="40" Quantity="40"/>Customer needs this ASAP     </Order>  </Employee>  </ROOT>'  -- Create an in memory representation of the document.  EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc  SELECT *  FROM OPENXML (@idoc, '/')        WITH (xmlcolumn ntext '@mp:xmltext')  EXEC sp_xml_removedocument @idoc 

Here's another example of the @mp:xmltext metaproperty in which we only want selected data to be placed in the overflow column. We'll also see a new flags parameter value of 9 . This value specifies two things: attribute-centric mapping, and that only the unconsumed data should be placed in the overflow column. Listing 8.13 shows the stored procedure that illustrates placing unconsumed data into an overflow column. Listing 8.14 shows the results.

Listing 8.13 The @mp:xmltext Metaproperty and Overflow Columns
 DECLARE @idoc int  DECLARE @xmldoc varchar(1000)  SET @xmldoc ='  <ROOT>  <Employee EmployeeID="5">     <LastName>Buchanan</LastName>     <FirstName>Steven</FirstName>     <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"            OrderDate="1996-07-04T00:00:00">     </Order>  </Employee>  <Employee EmployeeID="7">     <LastName>King</LastName>     <FirstName>Robert</FirstName>     <Order OrderID="10303" CustomerID="GODOS" EmployeeID="7"            OrderDate="1996-09-11T00:00:00" note="Almost out of inventory">        <Priority>First Class</Priority>     </Order>  </Employee>  </ROOT>'  -- Create an in memory representation of the document.  EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc  SELECT *  FROM OPENXML (@idoc, '/ROOT/Employee/Order', 9 )        WITH (OrderID   char(5),        ParentID        int         '@mp:parentid',        ParentLocalName varchar(20) '@mp:parentlocalname',        Remarks         ntext       '@mp:xmltext') 
Listing 8.14 Results of Specifying an Overflow Column
 OrderID ParentID    ParentLocalName      Remarks  ------- ----------- -------------------- ----------------------------------------- 10248   2           Employee             <Order CustomerID="VINET" EmployeeID="5"                                                  OrderDate="1996-07-04T00:00:00">                                           </Order>  10303   11          Employee             <Order CustomerID="GODOS" EmployeeID="7"                                                  OrderDate="1996-09-11T00:00:00"                                                  note="Almost out                                                  of inventory">                                                  <Priority>First Class</Priority>                                           </Order> 

Notice that the OrderID isn't included in the Remarks column. It was utilized in the OrderID column, so it was consumed.

Inserting Records via an HTML Form and OPENXML

At the beginning of this chapter, I made the point that OPENXML could be used to write data to a database, but then I spent the rest of this chapter showing you how to retrieve data.Well, now I'll show you how to do what I said could be done.

Let's take what we've learned to this point and apply it to an example that illustrates inserting data into a table.This example could very easily be expanded to real-world situations.You'll need to know a little about HTML and basic JavaScript.

The steps we'll perform in this example are as follows:

  • 1. Create a simple table for the data.

  • 2. Create a simple HTML form.

  • 3. Create an XML document from the form data.

  • 4. Pass the document to a template.

  • 5. Use OPENXML to insert the record.

Let's create the table first with the following SQL statement:

 CREATE TABLE Contact(ID int, LastName varchar(15), FirstName varchar(15)) 

Now let's create the HTML form, which is given in Listing 8.15 and illustrated in Figure 8.3. I want to emphasize the importance of the JavaScript portion of the HTML (the boldface type).This is the key function that takes the data entered in the form fields and then assembles and assigns the values to an XML element string.The string is assigned to the hidden field contactdata ; from here it will be posted to the destination Template.xml and become the input parameter.

Figure 8.3. Our HTML form.

graphics/08fig03.gif

Those of you who are unfamiliar with JavaScript and posting data on the Web should contact one of your Web developers for an explanation of how this works. It is not a difficult concept.

Listing 8.15 Our HTML Form
 <html>  <body>    <form action="http://iisserver/Nwind/templates/Template.xml" method="post">      <input type="hidden" id="cd" name="contactdata">      <input type="hidden" name="contenttype" value="text/xml">         ContactID: <input type=text id=cid value="1"><br>         First Name: <input type=text id=firstname value="Harry"><br>         Last Name: <input type=text id=lastname value="Smith"><br>      <input type=submit onclick="Insert_Contact(cd, cid,         lastname, firstname)" value="Insert Contact"><br><br>  <script>   function Insert_Contact(cd, cid, lastname, firstname)   {   cd.value = '<Contact ID="' + cid.value +   '" LastName="' + lastname.value + '" FirstName="' +   firstname.value + '"/>';   }   </script>  </form>  </body>  </html> 

Those of you who are familiar with the Java programming language will recognize the advantages it would offer here. Java servlets seem to have been made for this type of application. A disadvantage to this approach is the specialized knowledge required to develop servlets.

Listing 8.16 shows the stored procedure we need to execute, and Listing 8.17 shows the XML template file that executes the stored procedure.

Listing 8.16 Our Stored Procedure
 CREATE PROC sp_insert_contact @contactdata ntext  AS         DECLARE @hDoc int      EXEC sp_xml_preparedocument @hDoc OUTPUT, @contactdata      INSERT INTO Contact        SELECT *        FROM OPENXML(@hDoc, '/Contact')              WITH Contact       EXEC sp_xml_removedocument @hDoc 
Listing 8.17 Our XML Template
 <root xmlns:sql="urn:schemas-microsoft-com:xml-sql">  <sql:header>    <sql:param name="contactdata"></sql:param>  </sql:header>  <sql:query>    exec sp_insert_contact @contactdata  </sql:query>  </root> 

Updating Records via an HTML Form and OPENXML

Our last example shows how to update data in a table utilizing an HTML form and OPENXML. We'll use the same Contact table that we utilized in the example in the preceding section. We'll load the table with preset data and then issue the update command. One new wrinkle is the use of a text area for the XML document. This illustrates the insertion of an XML document that might have been created by a third-party application.

Figure 8.4 shows the HTML form.

Figure 8.4. The HTML form utilizing a text area for input.

graphics/08fig04.gif

The following example shows the SQL statement to clear and preload table data:

 TRUNCATE TABLE Contact  INSERT INTO Contact VALUES (501, 'Rick', 'Shelton')  INSERT INTO Contact VALUES (502, 'Corey', 'Tenney') 

Listings 8.18, 8.19, and 8.20 show the stored procedure needed, the template file, and the HTML file that starts everything, respectively.

Listing 8.18 The Stored Procedure
 DECLARE @hDoc int  exec sp_xml_preparedocument @hDoc OUTPUT,@contactdata  UPDATE Contact  SET       Contact.FirstName = XMLContact.FirstName,       Contact.LastName = XMLContact.LastName  FROM OPENXML(@hDoc, '/root/Contact')               WITH Contact XMLContact  WHERE Contact.ID = XMLContact.ID  EXEC sp_xml_removedocument @hDoc  SELECT   *  from      Contact  FOR XML AUTO 
Listing 8.19 The Template File
 <root xmlns:sql="urn:schemas-microsoft-com:xml-sql">  <sql:header>  <sql:param name="contactdata"></sql:param>  </sql:header>  <sql:query>exec sp_update_contact @contactdata  </sql:query>  </root> 
Listing 8.20 The HTML Page to Start Everything
 <html>  <body>  <form name="contact"        action="http://iisserver/Nwind/Templates/template.xml"        method="POST">  <h3>Data Update via OPENXML</h3><br>  <input type=hidden name="contenttype" value="text/xml">  <textarea name="contactdata" cols=50 rows=5>     <root>       <Contact ID=&quot;502&quot; LastName=&quot;Griffin&quot;  FirstName=&quot;John&quot;/>     </root>  </textarea>  <br><input type=Submit value="Submit">  </form>  </body>  </html> 

The following example shows the updated table contents utilizing an XML document output.

 <root xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <Contact ID="501" LastName="Rick" FirstName="Shelton" />    <Contact ID="502" LastName="Griffin" FirstName="John" />  </root> 


XML and SQL Server 2000
XML and SQL Server 2000
ISBN: 0735711127
EAN: 2147483647
Year: 2005
Pages: 104
Authors: John Griffin

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net