There are several different ways to get XML into SQL Server. First, you can just dump your XML into a nvarchar column in the database using a simple INSERT statement. Using this technique is just like entering any text into a column. With SQL Server, you can use the XML datatype rather than a text column.
There are three other ways of getting XML into your database:
• Shred your XML into multiple columns and rows in a single database call. To do this, you can use the OPENXML rowset provider. OPENXML provides a rowset view over an XML document and allows you to write T-SQL statements that parse XML.
• Use updategrams, which are data structures that can express changes to your data by representing a before-and-after image. SQLXML takes your updategram and generates the
necessary SQL commands to apply your changes.
• Use SQLXML’s XML BulkLoad provider. Using this provider, you can take a large set of XML
data and quickly load it into your SQL Server.
SQLXML is an additional set of technologies that include updategram support, the SQLXML BulkLoad provider, client-side FOR XML support, and SOAP support. For SQL Server 2000, SQLXML 3.0 shipped separately; it doesn’t need to run on a server. With SQL Server 2005 and SQL Server 2008, SQLXML 4.0 ships with the product, but it can also be redistributed on its own. Don’t confuse SQLXML with the SQL Server XML datatype.
Each technique for getting XML into the database has its strengths and weaknesses. If you are just looking for the fastest and highest performance way to get XML data into SQL Server, consider the BulkLoad provider. The BulkLoad provider doesn’t attempt to load all your XML into memory, but instead reads your XML data as a stream, interprets it, and loads it into your SQL Server. The BulkLoad provider is a separate component, so you cannot use it inside a stored procedure or user-defined function (UDF). You could use it in an extended stored procedure (XP) by calling out to it, but that is an uncommon scenario and has its own set of issues (XPs are complex, hard to debug, and can open up your server to security issues if written incorrectly).
On the other hand, OPENXML can be used in stored procedures and UDFs, since it ships as part of the native T-SQL language. You’ll pay a performance penalty for this integration though. OPENXML requires you to use a stored procedure, sp_xml_preparedocument, to parse the XML for consumption. This stored procedure loads a special version of the MSXML parser called MSXMLSQL to process the XML document and, in turn, loads the entire XML document into memory.
Updategrams are very useful for applications where you want to modify your database and you are OK with building an annotated schema and applying those changes through this annotated schema. SQLXML takes the updategram and translates it to SQL Data Manipulation Language (DML) statements. However, if you need to apply business logic to the SQL DML statements, you’ll be unable to use updategrams, since you cannot access the generated DML statements.
Don’t confuse SQLXML with the SQL/XML standard, also known as the SQLX standard. SQLX is an ANSI/ISO standard that defines how to make XML data work in relational databases. Microsoft is a member of the working committee for the SQLX standard. SQL Server currently doesn’t support the SQLX standard but provides equivalent functionality for the activities covered in the standard. For example, SQLX defines XML publishing, which SQL Server can do using the FOR XML statement. For the XML decomposition, you can use the XML datatype or OPENXML. Plus, there are things that the standard doesn’t define that SQL Server implements, such as combining XQuery into relational queries.
Source of Information : Apress Accelerated SQL Server
There are three other ways of getting XML into your database:
• Shred your XML into multiple columns and rows in a single database call. To do this, you can use the OPENXML rowset provider. OPENXML provides a rowset view over an XML document and allows you to write T-SQL statements that parse XML.
• Use updategrams, which are data structures that can express changes to your data by representing a before-and-after image. SQLXML takes your updategram and generates the
necessary SQL commands to apply your changes.
• Use SQLXML’s XML BulkLoad provider. Using this provider, you can take a large set of XML
data and quickly load it into your SQL Server.
SQLXML is an additional set of technologies that include updategram support, the SQLXML BulkLoad provider, client-side FOR XML support, and SOAP support. For SQL Server 2000, SQLXML 3.0 shipped separately; it doesn’t need to run on a server. With SQL Server 2005 and SQL Server 2008, SQLXML 4.0 ships with the product, but it can also be redistributed on its own. Don’t confuse SQLXML with the SQL Server XML datatype.
Each technique for getting XML into the database has its strengths and weaknesses. If you are just looking for the fastest and highest performance way to get XML data into SQL Server, consider the BulkLoad provider. The BulkLoad provider doesn’t attempt to load all your XML into memory, but instead reads your XML data as a stream, interprets it, and loads it into your SQL Server. The BulkLoad provider is a separate component, so you cannot use it inside a stored procedure or user-defined function (UDF). You could use it in an extended stored procedure (XP) by calling out to it, but that is an uncommon scenario and has its own set of issues (XPs are complex, hard to debug, and can open up your server to security issues if written incorrectly).
On the other hand, OPENXML can be used in stored procedures and UDFs, since it ships as part of the native T-SQL language. You’ll pay a performance penalty for this integration though. OPENXML requires you to use a stored procedure, sp_xml_preparedocument, to parse the XML for consumption. This stored procedure loads a special version of the MSXML parser called MSXMLSQL to process the XML document and, in turn, loads the entire XML document into memory.
Updategrams are very useful for applications where you want to modify your database and you are OK with building an annotated schema and applying those changes through this annotated schema. SQLXML takes the updategram and translates it to SQL Data Manipulation Language (DML) statements. However, if you need to apply business logic to the SQL DML statements, you’ll be unable to use updategrams, since you cannot access the generated DML statements.
Don’t confuse SQLXML with the SQL/XML standard, also known as the SQLX standard. SQLX is an ANSI/ISO standard that defines how to make XML data work in relational databases. Microsoft is a member of the working committee for the SQLX standard. SQL Server currently doesn’t support the SQLX standard but provides equivalent functionality for the activities covered in the standard. For example, SQLX defines XML publishing, which SQL Server can do using the FOR XML statement. For the XML decomposition, you can use the XML datatype or OPENXML. Plus, there are things that the standard doesn’t define that SQL Server implements, such as combining XQuery into relational queries.
Source of Information : Apress Accelerated SQL Server
|
|
0 comments
Post a Comment