Wednesday, September 21, 2011

Converting xml document data into a table rowset view in SQL server

OPENXML:-

OPENXML provides a rowset view over an XML document.

Syntax 

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) 
[WITH (SchemaDeclaration | TableName)]



sp_xml_preparedocument:-

Reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption. sp_xml_preparedocument limits the number of elements that can be open at one time to 256.
Syntax 
sp_xml_preparedocument
hdoc 
OUTPUT
[ , xmltext ]
[ , xpath_namespaces ] 

This example creates an internal representation of the XML image using sp_xml_preparedocument. A SELECT statement using an OPENXML rowset provider is then executed against the internal representation of the XML document.

DECLARE @BookDetails xml

DECLARE @handle INT

SET @BookDetails =

'<Books>
                  <Book>
                        <author_id>1</author_id>
                        <author_fname>Stephen</author_fname>
                        <author_lname>Smith</author_lname>
                        <book_title>Dotnet reference</book_title>
                        <price>30.50$</price>
                  </Book>
                  <Book>
                        <author_id>2</author_id>
                        <author_fname>John</author_fname>
                        <author_lname>Arthor</author_lname>
            <book_title>Donet complete review</book_title>
                        <price>35.50$</price>
                  </Book>
             </Books>'

EXEC sp_xml_preparedocument @handle OUTPUT, @BookDetails   
SELECT * FROM OPENXML (@handle, '/Books/Book', 2) WITH
  (author_id VARCHAR(5),
   author_fname NVARCHAR(500),
   author_lname NVARCHAR(500),
   book_title NVARCHAR(100),
   price nvarchar(10)  
  )

Output:-
author_id  author_fname   author_lname  book_title              Price  

1          Stephen        Smith         Dotnet reference        30.50$

2          John           Arthor        Donet complete review   35.50$


The OPENXML rowset provider creates a five-column rowset (author_id, author_fname, author_lname, book_title and price) from which the SELECT statement retrieves the all the necessary columns.

The flag value is set to 2 indicating attribute-centric mapping. Therefore, the XML attributes map to the columns in the rowset. The rowpattern specified as /Books/Book identifies the <Books> nodes to be processed.

If the same SELECT statement is executed with flags set to any value other than 2, indicating element-centric mapping, the values of author_id, author_fname, author_lname, book_title and price for both of the book records in the XML document are returned as NULL.



No comments :

Post a Comment