Blogger news

Blogger templates

Friday, March 11, 2011

SQL SERVER OPENXML

Data you stored in the database or in a variable in XML format can return to client by using OPEN XML function built in SQL SERVER 2008.

This process requires multiple steps.

1. Create a memory-resident data tree from the XML data.

2. Generate a rowset from the XML tree by using XPath to identify positions within the tree.

3. Process the data. You might want to return it to the client as a rowset or insert the data into other tables in your database.

4. To reclaim the memory, remove the data tree from memory.

Let me start with an example and will each of the steps mentioned above,

This code begins by assigning XML data to a variable. (You could just as easily pass the XML data to a procedure through a parameter or select it from a table.) Two stored procedures are used to prepare the XML document into a memory tree and destroy the tree when finished. The variable @ptrDoc is declared as an integer and points to this memory tree that the OPENXML function uses to walk through the memory tree.

Click image to expand:
The OPENXML function is divided into two pieces. The first section defines the context for the extraction. You must provide two parameters for this first section: the pointer to the prepared document and an XPath statement indicating the starting point for the extraction. Remember that XML can be deeply nested, so you have to tell the function where the extraction begins.

This function contains a third argument, into which is not mandatory. If we didn't pass that parameter it will take the default one. This is a flag that specifies which parts of the XML document the function will try to pull. You can choose to extract only attributes, only elements, or both. The values of the flags are listed

OPENXML Flags:

0- Default mappings (attributes)
1- Retrieve only attributes with names that match the WITH list
2 -Retrieve only elements with names that match the WITH list
3 -Retrieve both elements and attributes with names that match the WITH list


Any item in the WITH list that does not map to a specific mapped element or attribute in the XML tree will return NULL.



No comments:

Post a Comment