MetapropertiesAnytime 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
Table 8.3. Parent Attributes
Examples of Metaproperties UsageFirst 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 MetapropertiesDECLARE @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 :
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 MetapropertyDECLARE @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 ColumnsDECLARE @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 ColumnOrderID 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 OPENXMLAt 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:
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.
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 ProcedureCREATE 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 OPENXMLOur 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.
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 ProcedureDECLARE @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="502" LastName="Griffin" FirstName="John"/> </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> |