I have recently needed to solve the task for my own purpose: to calculate the number of external records linked by a foreign key for each record in a table (File). The task was solved for the specific structure of the File table, but if necessary, the solution can be reworked to a universal one.
I’ll clarify that the solution was developed for an unloaded database, without millions of records and an every minute update, so there was not much concern about the performance.
In this article, we will discuss typical errors that newbie developers may face with while designing T-SQL code. In addition, we will have a look at the best practices and some useful tips that may help you when working with SQL Server, as well as workarounds to improve performance.
Parsing data from XML using XQuery is a routine practice. In order to do this most effectively, little effort is required.
Suppose we need to parse data from the disk file with the following structure:
|
1 2 3 4 5 6 7 8 9 10 11 |
<tables> <table name="Accounting" schema="Production" object="Accounting"> <column name="Date" order="3" visible="1" /> <column name="DateFrom" order="5" visible="1" /> <column name="DateTo" order="6" visible="1" /> <column name="Description" order="4" visible="1" /> <column name="DocumentUID" order="1" visible="0" /> <column name="Number" order="2" visible="1" /> <column name="Warehouse" order="7" visible="1" /> </table> </tables> |
Use BULK INSERT, if you need to read data from a file:
|
1 2 |
SELECT BulkColumn FROM OPENROWSET(BULK 'D:\data.xml', SINGLE_BLOB) x sample xml file |