You maintain an Inventory database on an instance of SQL Server 2008. You have a large number of XML documents that you receive from a third-party processor each week.
At the end of each week, you want to shred the XML into relational data and populate existing tables in your database. After this process is complete, you want to archive the XML documents to folders on the file system. You want to accomplish this with the least development effort. Which action should you take?
A. Create an SSIS package to perform the needed tasks.
B. Use XQuery and the query() method.
C. Create a temporary table containing an xml data type column and use the OPENXML function.
D. Use FOR XML AUTO with the XMLDATA option.
Correct Answer: A
Explanation/Reference:
In this scenario, you need to shred multiple incoming XML documents into relational data and use it to populate existing database tables. You also need to repeat this process each week as new documents are received, and archive the XML documents to folders on the file system after they are processed. For this scenario, the best choice would be to use an SSIS package. SSIS allows you to extract, transform, and load data into data sources from a variety of formats, including XML documents. You would be able to manipulate the incoming XML as needed and add it to your existing tables, and then archive the data to the file system. You could then schedule the process to run weekly. This solution would minimize the development effort.
You should not use XQuery and the query() method. The query() method is used to retrieve nodes from existing xml columns or variables. In this scenario, you need to load the data and shred it as it is loaded, and you need to repeat this weekly. Therefore, using an SSIS package to perform the task would be better.
You should not create a temporary table containing an xml data type column and use the OPENXML function because this would require more coding than necessary. You would also need to implement a mechanism for archiving the XML documents and a mechanism to perform the tasks weekly. OPENXML is a rowset provider function that creates a relational view of data contained in an XML document. This function can be used to shred XML into relational data by including the function in a SELECT statement as if it were a table or view. To use OPENXML, you first call the sp_xml_preparedocument system stored procedure to obtain a handle to the XML instance. Then, you can extract data from the XML document into relational format. After shredding the XML, you call the sp_xml_removedocument stored procedure to remove the document handle and free up resources.
You should not use FOR XML AUTO with the XMLDATA option because the FOR XML clause is used with a SELECT statement to output relational data in XML format. In previous versions of SQL Server, the XMLDATA option allowed you to include an XML-Data Reduced (XDR) schema in the generated output. In situations where this might be applicable, you should use the XMLSCHEMA option instead, because the XMLDATA option has been deprecated.
