OPENXML allows access to XML data as though it is a relational rowset.  It provides rowset view of internal representation of XML document. The records in the rowset can be stored in database table. Above illustration explain the process of OPENXML

 OPENXML syntax:

 

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )

[ WITH ( SchemaDeclaration | TableName ) ]

Arguments: 

idoc:

            idoc is the document handle of the internal representation of an XML document. 

rowpattern:

            rowpattern is the xPath representation to identify the nodes           

flags:

               flags indicates the mapping that should be used between the XML data and the relational rowset. Following table represents flags byte value and description 

 

Byte Value

Description

0

Defaults to attribute-centric mapping

1

Use the attribute-centric mapping it can be combined with XML_ELEMENTS

2

Use the element centric mapping  it can be combined with XML_ATTRIBUTES

8

Can be combined with XML_ELEMENTS or XML_ATTRIBUTES

  

To write queries against an XML document by using OPENXML, you must first call sp_xml_preparedocument. This parses the XML document and returns a handle to the parsed document that is ready for consumption. The parsed document is a document object model (DOM) tree representation of various nodes in the XML document. The document handle is passed to OPENXML. OPENXML then provides a rowset view of the document, based on the parameters passed to it. 

            The internal representation of an XML document must be removed from memory by calling the sp_xml_removedocument system stored procedure to free the memory.


Query: 

     declare @XMLDocHandle INT

          declare @xml_data varchar(MAX)='<PersonalDetails>

                                            <Person>

                                               <name>Sachin</name>

                                               <age>24</age>

                                               <sex>Male</sex>

                                               <city>Chennai</city>  

                                            </Person>

                                            <Person>

                                               <name>Mathews</name>

                                               <age>30</age>

                                               <sex>Male</sex>

                                               <city>New Delhi</city>  

                                            </Person>

                                          </PersonalDetails>'         

          EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @xml_data 

          INSERT INTO PersonalDetails 

                                (Name,

                                Age,

                                Sex, 

                                City                                           

                                )                                         

        SELECT NULLIF(name,'')

                     ,NULLIF(age,'')

                     ,NULLIF(sex,'')

                     ,NULLIF(city,'')               

          FROM  OPENXML (@XMLDocHandle, N'/PersonalDetails/Person',2)

                   WITH

                    (name varchar(50),

                    age varchar(50),

                     sex varchar(50),

                     city varchar(50)

                    )        

          EXEC sp_xml_removedocument @XMLDocHandle         

          select * from PersonalDetails 

          

Note:

          For more information please follow the link given below.

http://technet.microsoft.com/en-us/library/ms175160.aspx

cytotec abortion

an abortion pill buy abortion pills online
medical abortion pill online cytotec abortion abortion pill


View demo Download
http://www.dotnetfox.com/Document/1089/0Demo1.gif

If you enjoyed this article, get email updates (it's free).


Related Articles
  • Create Comma Separated List From Table Using STUFFIn this article I’m going to explain how to create comma separated list from table. If we want to create comma separated list from table, we can use different methods given below,1. CURSOR 2. COALESCE() 3. STUFF() Here I’m going to use STUFF() scalar function to create comma separated list.
  • Getting table column with description in SQL ServerIn this article I’m going to explain how to get table column description in SQL Server. An information schema view is one of several methods SQL Server provides for obtaining metadata.
  • How to fetch data from XML to store SQL Server database table using OPENXMLOPENXML allows access to XML data as though it is a relational rowset. It provides rowset view of internal representation of XML document. The records in the rowset can be stored in database table. Above illustration explain the process of OPENXML. To write queries against an XML document by using OPENXML, you must first call sp_xml_preparedocument. This parses the XML document and returns a hand...

Comments
comments powered by Disqus

Thanks for downloading..!!

×
Subscribe via Email
    
message to display in fancybox