document Function - function TxtToXml

TxtToXml

This functions can open a tab file and get an xml.

Parameters:
@path
the directoy of file

@file
the file to read

@encoding * 1->UTF8 * 2-> UTF8 BOM * 3-> UTF7 * 4-> UTF32 * Other, default 1

@first_row
the position of first row to read

@delimitator
the char/s that delimit the columns

@decimale
the decimal symbol of numbers.
If not defined (‘’) will use the default of system.

@types
comma list of values or one value (for all columns): * 'nothing' - string * 'd' - date * 'n' - numeric
(for example: ',,,d,n' than mean column 4 is a date and last is numeric)

@positions
comma list of int values ‘1,2,4,9,...’
Map the previous definition of columns with the position of text columns.
If not defined (‘’) will import in the text sequence.

@options
comma list ‘,,yyyyMMdd,ddMMyy,...’ or single vale ‘yyyyMMdd’
It s the date format.
If the column it s “date/datetime/smalldatetime” sp try to cast it with the defined format.
If not defined (‘’) will use the default system settings.

@instestazione * True: * file has header; * False: * file hasn t header.

@buffer_row
0 use the default value.
To find the best value it s necessary to try with some values because it depends by hardware of server.

@Use_min_rows * True: * If the text has some rows with less columns of the definition will continue to the next. * False: * if a row has less columns will return an error.

example:

select *
from TxtToXml('C:\path\','file.txt',0,0,'|','.',',,d,n','','yyyyMMdd',1,0,1)t1

using this file

c1|c2|c3|c4
va1|va2|20220101|1
vb1|vb2|19990105|5
vc1|vc2|19000112|524
vd1|vd2|20501231|110

the result is

<DocumentElement>
  <table>
    <c1>va1</c1>
    <c2>va2</c2>
    <c3>2022-01-01T00:00:00+01:00</c3>
    <c4>1</c4>
    <rownum>1</rownum>
  </table>
  <table>
    <c1>vb1</c1>
    <c2>vb2</c2>
    <c3>1999-01-05T00:00:00+01:00</c3>
    <c4>5</c4>
    <rownum>2</rownum>
  </table>
  <table>
    <c1>vc1</c1>
    <c2>vc2</c2>
    <c3>1900-01-12T00:00:00+01:00</c3>
    <c4>524</c4>
    <rownum>3</rownum>
  </table>
  <table>
    <c1>vd1</c1>
    <c2>vd2</c2>
    <c3>2050-12-31T00:00:00+01:00</c3>
    <c4>110</c4>
    <rownum>4</rownum>
  </table>
</DocumentElement>

to manage it like a table use the sql xml functions

select x.XmlCol.value(N'rownum[1]', N'int') as r,
       x.XmlCol.value(N'c1[1]', N'nvarchar(10)') as c1,
       x.XmlCol.value(N'c2[1]', N'nvarchar(10)') as c2,
       convert(date,ltrim(rtrim(x.XmlCol.value(N'c3[1]', N'nvarchar(10)')))) as c3,
       x.XmlCol.value(N'c4[1]', N'int') as c4
from TxtToXml('C:\path\','file.txt',0,0,'|','.',',,d,n','','yyyyMMdd',1,0,1)t1
CROSS APPLY t1.tablexml.nodes('//DocumentElement/table') x(XmlCol)
where t1.p>0

r   c1  c2  c3  c4
1   va1 va2 2022-01-01  1
2   vb1 vb2 1999-01-05  5
3   vc1 vc2 1900-01-12  524
4   vd1 vd2 2050-12-31  110