StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POImporting and validating XML file using SSIS or just plain T-SQL?
text
Body
copied!<p>What is the best practice when importing and validating an XML file to a single table (flattened) in SQL Server ?</p> <p>I've a XML file which contains about 15 complex types which are all related to a single parent element. The SSIS design could look like this: <img src="https://i.stack.imgur.com/0YQ46.png" alt="SSIS"> But it's getting very complicated with all those (15) joins.</p> <p>Is it maybe a better idea to just write T-SQL code to :<br/> 1) Import the XML into a column which is of the type XML and is linked to a XSD-schema.<br/> 2) Use this code:<br/></p> <pre><code>TRUNCATE TABLE XML_Import INSERT INTO XML_Import(ImportDateTime, XmlData) SELECT GETDATE(), XmlData FROM ( SELECT * FROM OPENROWSET (BULK 'c:\XML-Data.xml', SINGLE_BLOB) AS XMLDATA ) AS FileImport (XMLDATA) delete from dbo.UserFlat INSERT INTO dbo.UserFlat SELECT user.value('(UserIdentifier)', 'varchar(8)') as UserIdentifier, user.value('(Emailaddress)', 'varchar(70)') as Emailaddress, businessaddress.value('(Fax)', 'varchar(70)') as Fax, employment.value('(EmploymentData)', 'varchar(8)') as EmploymentData, -- More values here ... FROM XML_Import CROSS APPLY XmlData.nodes('//user') AS User(user) CROSS APPLY user.nodes('BusinessAddress') AS BusinessAddress(businessaddress) CROSS APPLY user.nodes('Employment') AS Employment(employment) -- More 'joins' here ... </code></pre> <p>to fill the 'UserFlat' table ?<br/>Some disadvantages are that you have to manually type the SQLcode, but the advantage here is that I have more direct control how the elements are processed and converted. But I don't know if there are any performance differences between processing XML in SSIS and processing the XML with T-SQL XML statements.</p> <p><br/> Note that some other requirements are:</p> <ol> <li>Error handling : in case of an error, an email must be send to a person.</li> <li>Able to process multiple input files with a specific file name pattern : XML_{date}_{time}.xml</li> <li>Move the processed XML files to a different folder.</li> </ol> <p>Please advice.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload