XML Tips

One very common process that occurs in most SQL Server environments is the need to import and export data. With SQL Server 7.0 and 2000 we had DTS (Data Transformation Services) and now with SQL Server 2005 we have SSIS (SQL Server Integration Services). These are great GUI environments and give you a lot of flexibility to move data in and out of SQL Server. In addition to these GUI tools there is the tried and true BCP (Bulk Copy Program) that has been around for a lot longer then DTS or SSIS.

Sometimes there is a need to process files in a folder, but first you need to determine which files need to be processed compared to older files that have already been processed. There are several ways that this can be done, but in this tip I show you a way this can be done using SQL Server and XML.

With SQL Server 2005's native support of XML, XML seems to be popping up more and more in applications and as output from a variety of SQL Server administrative commands. In particular organizations, working with XML has become the norm for particular needs and applications. Unfortunately, creating, managing and editing XML data has still been a challenge with the native SQL Server tool set. Although a number of the native SQL Server commands (Cross Walk - SQL Server Query Plans) can have data returned in an XML format, working with the data from that point forward can be a major challenge, especially with large result sets. As such, how should I go about working with XML result sets directly from SQL Server 2005?

We have a number of instances where we coded stored procedures to handle transforming an input parameter that is an XML document into a rowset. Prior to SQL Server 2005 we have always used the OPENXML rowset provider to parse the XML and produce a rowset. What new functionality is there in SQL Server 2005 to accomplish this?

With XML being used in so many application and data exchange capacities, extracting XML from SQL Server should not be a challenge. Yet, some organizations are building complex applications to do so and overlooking some of the native SQL Server features. If the data tier has a viable option to extract XML in a native format, should that option be considered?

Over the years I have had to adapt to many issues presented before me as a Database Administrator and I have gathered many scripts to deal with them. One that has become quite a topic is XML integration for transactions to the database. In developing this process, my research of resources on the topic throughout the internet, seemed to only discuss primary functions and fundamental application. The following production code is what I used for inserting and updating database tables using XML as the input. These scripts are for processing data for any table to insert or update data. The support functions provided, retrieve the table schema with their data types, functions to deal with XML dates, primary keys of the table and what fields can b

The institution I work with receives data files in a variety of formats for import into a central SQL Server 2000 repository. We have a good handle on importing the files; however, some of the providers want to send data using XML files. Is there a simple process I can create to import these files?

Over the years I have had to adapt to many issues presented before me as a Database Administrator and I have gathered many scripts to deal with them. One that has become quite a topic is XML integration for transactions to the database. In developing this process, my research of resources on the topic throughout the internet, seemed to only discuss primary functions and fundamental application. The following production code is what I used for inserting and updating database tables using XML as the input. These scripts are for processing data for any table to insert or update data. The support functions provided, retrieve the table schema with their data types, functions to deal with XML dates, primary keys of the table and what fields can b

One very common process that occurs in most SQL Server environments is the need to import and export data. With SQL Server 7.0 and 2000 we had DTS (Data Transformation Services) and now with SQL Server 2005 we have SSIS (SQL Server Integration Services). These are great GUI environments and give you a lot of flexibility to move data in and out of SQL Server. In addition to these GUI tools there is the tried and true BCP (Bulk Copy Program) that has been around for a lot longer then DTS or SSIS.

We have a number of instances where we coded stored procedures to handle transforming an input parameter that is an XML document into a rowset. Prior to SQL Server 2005 we have always used the OPENXML rowset provider to parse the XML and produce a rowset. What new functionality is there in SQL Server 2005 to accomplish this?

The institution I work with receives data files in a variety of formats for import into a central SQL Server 2000 repository. We have a good handle on importing the files; however, some of the providers want to send data using XML files. Is there a simple process I can create to import these files?

With XML being used in so many application and data exchange capacities, extracting XML from SQL Server should not be a challenge. Yet, some organizations are building complex applications to do so and overlooking some of the native SQL Server features. If the data tier has a viable option to extract XML in a native format, should that option be considered?

Sometimes there is a need to process files in a folder, but first you need to determine which files need to be processed compared to older files that have already been processed. There are several ways that this can be done, but in this tip I show you a way this can be done using SQL Server and XML.

With SQL Server 2005's native support of XML, XML seems to be popping up more and more in applications and as output from a variety of SQL Server administrative commands. In particular organizations, working with XML has become the norm for particular needs and applications. Unfortunately, creating, managing and editing XML data has still been a challenge with the native SQL Server tool set. Although a number of the native SQL Server commands (Cross Walk - SQL Server Query Plans) can have data returned in an XML format, working with the data from that point forward can be a major challenge, especially with large result sets. As such, how should I go about working with XML result sets directly from SQL Server 2005?

Over the years I have had to adapt to many issues presented before me as a Database Administrator and I have gathered many scripts to deal with them. One that has become quite a topic is XML integration for transactions to the database. In developing this process, my research of resources on the topic throughout the internet, seemed to only discuss primary functions and fundamental application. The following production code is what I used for inserting and updating database tables using XML as the input. These scripts are for processing data for any table to insert or update data. The support functions provided, retrieve the table schema with their data types, functions to deal with XML dates, primary keys of the table and what fields can b

The institution I work with receives data files in a variety of formats for import into a central SQL Server 2000 repository. We have a good handle on importing the files; however, some of the providers want to send data using XML files. Is there a simple process I can create to import these files?

Sometimes there is a need to process files in a folder, but first you need to determine which files need to be processed compared to older files that have already been processed. There are several ways that this can be done, but in this tip I show you a way this can be done using SQL Server and XML.

We have a number of instances where we coded stored procedures to handle transforming an input parameter that is an XML document into a rowset. Prior to SQL Server 2005 we have always used the OPENXML rowset provider to parse the XML and produce a rowset. What new functionality is there in SQL Server 2005 to accomplish this?

With SQL Server 2005's native support of XML, XML seems to be popping up more and more in applications and as output from a variety of SQL Server administrative commands. In particular organizations, working with XML has become the norm for particular needs and applications. Unfortunately, creating, managing and editing XML data has still been a challenge with the native SQL Server tool set. Although a number of the native SQL Server commands (Cross Walk - SQL Server Query Plans) can have data returned in an XML format, working with the data from that point forward can be a major challenge, especially with large result sets. As such, how should I go about working with XML result sets directly from SQL Server 2005?

One very common process that occurs in most SQL Server environments is the need to import and export data. With SQL Server 7.0 and 2000 we had DTS (Data Transformation Services) and now with SQL Server 2005 we have SSIS (SQL Server Integration Services). These are great GUI environments and give you a lot of flexibility to move data in and out of SQL Server. In addition to these GUI tools there is the tried and true BCP (Bulk Copy Program) that has been around for a lot longer then DTS or SSIS.

With XML being used in so many application and data exchange capacities, extracting XML from SQL Server should not be a challenge. Yet, some organizations are building complex applications to do so and overlooking some of the native SQL Server features. If the data tier has a viable option to extract XML in a native format, should that option be considered?