15: Modifying Databases Using SQLXML in Visual Studio .NET

Create Data Matrix in Visual Studio .NET 15: Modifying Databases Using SQLXML

15: Modifying Databases Using SQLXML
Scanning DataMatrix In .NET Framework
Using Barcode Control SDK for VS .NET Control to generate, create, read, scan barcode image in .NET applications.
Data Matrix Printer In .NET
Using Barcode encoder for .NET framework Control to generate, create DataMatrix image in VS .NET applications.
The xmltext parameter accepts any type of character data (char, varchar, nchar, nvarchar, text, or ntext). xpath_namespaces is an optional parameter that is used to provide a namespace declaration for row and column expressions in OpenXML(). The default value is as follows:
Reading ECC200 In .NET
Using Barcode decoder for .NET Control to read, scan read, scan image in Visual Studio .NET applications.
Generating Bar Code In .NET Framework
Using Barcode printer for VS .NET Control to generate, create bar code image in Visual Studio .NET applications.
<root xmlns:mp="urn:schemas-microsoft-com:xml-metaprop">
Barcode Recognizer In .NET Framework
Using Barcode recognizer for .NET framework Control to read, scan read, scan image in .NET framework applications.
Data Matrix Encoder In Visual C#
Using Barcode creation for .NET Control to generate, create Data Matrix image in Visual Studio .NET applications.
The stored procedure returns a non-zero value when SQL Server cannot prepare the document. You should use this return value to perform error handling in the usual manner. In the following example an XML document is loaded in memory and its handle is recorded in the @intDoc variable:
Drawing Data Matrix 2d Barcode In VS .NET
Using Barcode creator for ASP.NET Control to generate, create DataMatrix image in ASP.NET applications.
Data Matrix 2d Barcode Generator In VB.NET
Using Barcode creation for .NET Control to generate, create ECC200 image in .NET applications.
DECLARE @intDoc int DECLARE @chvXMLDoc varchar(8000) -- sample XML document SET @chvXMLDoc =' <root> <Equipment EquipmentID="1" Make="Toshiba" Model="Portege 7020CT"> <Inventory InventoryID="5" StatusID="1" EquipmentID="1"/> <Inventory InventoryID="12" StatusID="1" EquipmentID="1"/> </Equipment> </root>' --Load the XML document into memory. EXEC sp_xml_preparedocument @intDoc OUTPUT, @chvXMLDoc
Bar Code Creator In .NET
Using Barcode generation for VS .NET Control to generate, create bar code image in .NET framework applications.
Make Barcode In .NET
Using Barcode generation for Visual Studio .NET Control to generate, create barcode image in .NET applications.
Closing the Document
2D Barcode Generation In .NET Framework
Using Barcode printer for VS .NET Control to generate, create Matrix Barcode image in .NET applications.
Leitcode Generator In .NET
Using Barcode printer for VS .NET Control to generate, create Leitcode image in .NET framework applications.
As soon as it is no longer used, the document should be removed from memory using sp_xml_removedocument. This stored procedure uses very simple syntax:
Data Matrix ECC200 Printer In Java
Using Barcode creator for BIRT Control to generate, create Data Matrix image in BIRT reports applications.
Draw EAN / UCC - 13 In Visual Basic .NET
Using Barcode generation for .NET Control to generate, create UCC.EAN - 128 image in Visual Studio .NET applications.
exec sp_xml_removedocument hdoc
Barcode Encoder In .NET Framework
Using Barcode generator for Reporting Service Control to generate, create bar code image in Reporting Service applications.
UCC - 12 Drawer In Java
Using Barcode creation for Java Control to generate, create GS1 128 image in Java applications.
The hdoc parameter is a handle for a loaded XML document:
UCC - 12 Generation In Objective-C
Using Barcode maker for iPad Control to generate, create GS1-128 image in iPad applications.
Recognizing DataMatrix In None
Using Barcode recognizer for Software Control to read, scan read, scan image in Software applications.
remove the XML document from memory EXEC sp_xml_removedocument @intDoc
Creating ANSI/AIM Code 39 In None
Using Barcode printer for Font Control to generate, create Code-39 image in Font applications.
Generate EAN / UCC - 13 In None
Using Barcode maker for Software Control to generate, create UCC.EAN - 128 image in Software applications.
NOTE
The memory is not released until sp_xml_removedocument is called, and hence it should be called as soon as possible.
SQL Server 2000 Stored Procedure & XML Programming
Retrieving the XML Information
OpenXML() is a rowset provider that provides access to the internal tree memory structure that contains the information in an XML document. It has the following syntax:
OpenXML(hdoc, rowpattern, flags) [With (SchemaDeclaration | TableVariable)]
hdoc is a handle that points to the tree containing the XML data. rowpattern is the XPath string used to identify nodes that need to be processed. flags is an optional parameter that controls the way that data from the XML document is mapped to the rowset and how data is to be copied to the overflow property (I will explain this a little later). SchemaDeclaration is a declaration of the structure in which data will be returned. Alternatively, it is possible to use the name of a table variable (TableVariable) instead. The rowset will be formed using the structure of the table variable. The SchemaDeclaration can be composed using the following syntax:
ColName ColType [ColPattern | MetaProperty] [, ColName ColType [ColPattern | MetaProperty]...]
ColName is the name and ColType is the data type of the column. This structure is very similar to the table structure of the Create Table statement. ColPattern is an optional parameter that defines how a column is to be mapped to the XML node. A MetaProperty is specified to extract metadata such as data types, node types, and namespace information. Finally, take a look at an example that uses all these constructs:
DECLARE @intDoc int DECLARE @chvXMLDoc varchar(8000) -- sample XML document SET @chvXMLDoc = '<root> <Equipment EquipmentID="1" Make="Toshiba" Model="Portege 7020CT"> <Inventory InventoryID="5" StatusID="1" EquipmentID="1"/> <Inventory InventoryID="12" StatusID="1" EquipmentID="1"/> </Equipment> <Equipment EquipmentID="2" Make="Sony" Model="Trinitron 17XE"/> <Equipment EquipmentID="4" Make="HP" Model="LaserJet 4"/> <Equipment EquipmentID="5" Make="Bang & Olafson" Model="V4000"> <Inventory InventoryID="8" StatusID="1" EquipmentID="5"/> </Equipment>
15: Modifying Databases Using SQLXML
<Equipment EquipmentID="6" Make="NEC" Model="V90"> <Inventory InventoryID="6" StatusID="2" EquipmentID="6"/> </Equipment> </root>' --Load the XML document into memory. EXEC sp_xml_preparedocument @intDoc OUTPUT, @chvXMLDoc -- SELECT statement using OPENXML rowset provider SELECT * FROM OPENXML (@intDoc, '/root/Equipment/Inventory', 8) WITH (InventoryID int '@InventoryID', StatusID int '@StatusID', Make varchar(25) '../@Make', Model varchar(25) '../@Model', Comment ntext '@mp:xmltext') -- remove the XML document from memory EXEC sp_xml_removedocument @intDoc
The result is shown in Figure 15-1.
Figure 15-1
Use of OpenXML() as a rowset provider
SQL Server 2000 Stored Procedure & XML Programming
In the preceding example, the OpenXML() rowset provider is used in a Select statement:
SELECT * From Openxml (@intDoc, '/root/Equipment/Inventory', 8) WITH (InventoryID int '@InventoryID', StatusID int '@StatusID', Make varchar(25) '../@Make', Model varchar(25) '../@Model', Comment ntext '@mp:xmltext')
The rowpattern parameter specifies that information will be extracted (mostly) from Inventory nodes ('/root/Equipment/Inventory'). The third parameter of the OpenXML() clause sets the way in which the overflow property is to be filled. In the preceding example, the last column (Comment) was filled with metadata provided by the XML parser (since the column is associated with the @mp:xmltext attribute). Because the third parameter of OpenXML() is set to 8 (the XML_NOCOPY constant), the overflow property does not contain the nodes that are extracted into the rowset. Only nodes that are not included in the rowset are recorded. Other columns in the rowset are filled from the attribute data. If you remember XPath (see the XPath section in 13), the @ character is used as an abbreviation that points to attribute nodes. The Make and Model columns are not in the same group of nodes as InventoryID and StatusId. Since they are attributes of the Equipment node, ColPattern has to refer to the parent node ('../@Model') first. Table 15-1 shows a list of possible values of the flags parameter. XML_NOCOPY could be combined (logical OR) with XML_ATTRIBUTES (1 + 8 = 9) or XML_ELEMENTS (2 + 8 = 10). This flag can be used to generate either a string with the overflow information or a string with a complete branch of Mnemonic
XML_ATTRIBUTES XML_ELEMENTS XML_DEFAULT XML_NOCOPY Table 15-1
Copyright © OnBarcode.com . All rights reserved.