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