StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>SQL Server 2005 allows to store XML data in two ways: - As a Rowset & - An XML Column</p> <p><strong>Storing XML Data in a Rowset</strong></p> <ul> <li>If your client has sent you data as an XML document and you need to store this data in a database table, first you need to "shred" the XML data. SQL Server allows you to shred XML data by using the <strong>OPENXML</strong> function and its related stored procedures.</li> </ul> <p>Shredding XML document involves the following tasks:</p> <ul> <li>i) Parse the XML document SQL server 2005 provides the <strong><code>sp_xml_preparedocument</code></strong> stored procedure to parse the xml document. This stored procedure reads the xml document and parses it with the MSXML parser. The parsed document is an internal tree representation of various nodes in the xml doc such as elements,attributes,text and comments.</li> <li>ii)Retrieve the rowset from the tree. Now you need to extract the data from the available xml data. You use <strong>openxml</strong> function for this purpose and to generate an in-memory rowset from the parsed data. Syntax: <code>openxml(idoc int[in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)]</code><br> idoc- specifies the doc handle of the internal tree representation of xml doc rowpattern- specifies the XPath pattern used to identify the nodes to be processed as rows. flags- indicates the mapping that should be used between xml data and relational rowset, and how the spill-over column should be filled. It is optional and can have 0,1,2,3 to use default mapping, to retrieve attribute values, to retrieve element values and to retrieve both values respectively. SchemaDeclaration- specifies the rowset schema declaration for the columns to be returned by using a combination of column names, data types and patterns. TableName- specifies the table name that can be given instead of SchemaDeclaration, if a table with the desired schema already exists and no patterns are required.</li> <li>iii)Store the data from the rowset. You can use the rowset created by <strong>openxml</strong> to store the data, in the same way that you would sue any other rowset. You can insert the rowset data into permanent tables in a database.</li> <li>iv)Clear the memory. You need to release the memory where you stored the rowset. For this, you use the <strong><code>sp_xml_removedocument</code></strong> stored procedure.</li> </ul> <p>For example, following is the data available in an XML doc:</p> <pre><code> DECLARE @Doc int DECLARE @XMlDoc nvarchar(1000) SET @XMLDoc = N'<ROOT> <Customer CustomerID="JHO1" Name="Jack"> <Order OrderID="1001" CustomerID="JH01" OrderDate="2009-07-04T00:00:00"> <OrderDetail ProductID="11" Quantity="12"/> <OrderDetail ProductID="22" Quantity="10"/> </Order> </Customer> <Customer CustomerID="SG01" Name="Steve"> <Order OrderID="1002" CustomerID="SG01" OrderDate="2009-08-16T00:00:00"> <OrderDetail ProductID="32" Quantity="3"/> </Order> </Customer> </ROOT>' </code></pre> <p>To view this xml data in a rowset, you need to execute the following statements:</p> <p><strong>1.</strong> Create internal representation of xml document <code>EXEC sp_xml_preparedocument @Doc OUTPUT, @XMLDoc</code></p> <p><strong>2.</strong> Execute the following query to store the data in a table using OPENXML function:</p> <pre><code>INSERT INTO CustomerDetails SELECT * FROM openxml (@Doc, '/ROOT/Customer' , 1) WITH (CustomerID varchar(10), Name varchar(20) ) </code></pre> <p>The data will be displayed as in the table:</p> <hr> <pre><code>CustomerID | Name | ___________|_________| JH01 | Jack | | | SG01 | Steve | ___________|_________| </code></pre> <p><strong>3.</strong> Remove the internal tree from the memory by executing </p> <pre><code>EXEC sp_xml_removedocument @Doc </code></pre> <p>You're done now.</p> <p>I think this method would help rather than the other one, ie, storing xml data as XML Column.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload