Shredding is the process of converting XML documents to relational format-viewing your XML data as if it were a relational table. SQL Server provides two options for shredding XML data. The first, simplest option takes advantage of the xml data type .nodes() method. This method accepts an XPath-style path expression and returns a rowset consisting of xml data type values. As you can see, the .nodes() method has to be aliased with both a table name and a column name. In this case, we used the alias Tab(Col) for simplicity. We used the xml data type .value() method to retrieve scalar values from the Col column of the result set. The xml data type .nodes() method is a fairly simple way to shred your XML data, but there's a second option. The OPENXML rowset provider function is backwardcompatible with SQL Server 2000. Like the .nodes() method, the OPENXML rowset provider OPENXML can be used to shred any binary or character data type, or (beginning with SQL Server 2005) the xml data type. OPENXML is a little more complicated than the .nodes() method. To use it, you first have to call the sp_xml_preparedocument stored procedure, which returns a handle to the XML document. This procedure invokes the COM-based Microsoft XML Core Services Library (MSXML) to convert your textual XML data to an internal XML Document Object Model (XML DOM) representation. sp_xml_preparedocument allocates memory for the XML DOM representation of your XML and returns an integer identifier, known as a document handle.
Once you've created the XML DOM representation of your XML data, you can call the OPENXML function to shred it. OPENXML takes three parameters: the document handle you generated previously with the call to sp_xml_preparedocument, an XPath-style path expression to indicate the nodes to shred, and an optional integer flag value that determines whether the input XML is element-centric or attribute-centric. The OPENXML function WITH clause allows you to specify the structure of the output rowset. Each column of the rowset can be defined with a column name, T-SQL data type, and a path expression indicating where the data should be pulled from relative to the current context node. Finally, OPENXML requires you to clean up after yourself by calling sp_xml_ removedocument with the previously generated document handle. This procedure removes the XML DOM representation of your XML data and frees up the memory MSXML was using.
Different XML-based standards may have element names in common. It is not uncommon to see different XML-based standards that specify elements named Name and Price, for instance. Each of these element names can have a completely different meaning, depending on the standard. In one standard, the Name element might be a customer name; in another standard, it might be a product name. To differentiate between the different uses for the same name in different standards, XML provides the concept of XML namespace. An XML namespace is a qualifier for XML elements and attributes that allows you to associate them with XML documents. Using an XML namespace, you can differentiate between the Name elements used in different standards. The XML namespace associates a namespace prefix with a URI for a document. If you are using XQuery to query data that contains one or more explicit namespaces, you can use the XQuery declare element's namespace option to define the namespace in your query. We've defined two namespaces in the XML document: one to denote customer-specific elements and another for product-specific elements. When we query the data we use the XQuery declare namespace statement to assign a namespace prefix to a URI before we define the path expression. Once we've declared the namespace, we can use the namespace prefix to differentiate between elements in the path expression-even elements with the same local name. SQL Server also provides a WITH XMLNAMESPACES clause that can be used with queries, common table expressions (CTEs), and data manipulation language (DML) statements to declare your XML namespaces.
XQuery is a handy query language when you know the structure of your XML document in advance, because you can specify the hierarchical path structure to get to the elements you are interested in. But what happens when you don't know the XML document structure in advance? In some cases you might want to grab the names and values of the elements in your XML document no matter where they occur. In those cases, you can take advantage of the xml data type's .nodes() method and XQuery wildcard querying. In XQuery, the asterisk (*) can stand in as a wildcard in your path expressions. This sample query takes advantage of the XQuery wildcard character to match every node in the XML data, regardless of location or XML namespace. We also use the XQuery fn:namespace-uri and fn:local-name functions to retrieve the XML namespace URI and local name for every element found.
The OPENROWSET function has a BULK option that allows you to load data into SQL Server directly from the filesystem. Using this option, you can load a single file from the filesystem directly into a variable or column in a table. For our example, we will assume an XML file named state-list.xml exists in the root directory of your C: drive. You can use the OPENROWSET function with BULK option to load this XML file from the filesystem into an xml variable. A few things you need to keep in mind with this method:
The SQL Server service must have access to the drive, directory, and file you are trying to load.
The source file name must be a string literal. You can't replace it with a variable name. If you want to use a variable name, you'll have to use this method with dynamic SQL.
We used the SINGLE_BLOB option, because it is the preferred method. SINGLE_BLOB supports all Windows encoding conversions. You can also use SINGLE_CLOB for character files or SINGLE_NCLOB for national character (Unicode) files.
Our website is not responsible for the information contained by this article. Articleinput.com is a free articles resource thus practically any visitor can submit an article. However if you notice any copyrighted material, please contact us and we will remove the article(s) in discussion right away.
Note: This article was sent to us by: Leslie Glover at 01092010
1. Definitions and uses of functions in Python
All articles are property of their respective authors. Please read our Privacy Policy!
© 2009 ArticleInput.com.