How to import from XML file in SQL Server Import and Export Wizard?

Question

I have data and schema for a table as a single XML file. How do I import this using the SQL Server Import and Export Wizard?

Should I use a "Flat File Source" as my Data Source? Or what?

[For info, I exported the XML from VistaDB, but I have not yet got as far as the point where the system which created the data might be an issue.]

Answer 1

As far as I know MS SQL Server Management Studio does not have a tool to upload XML to a table. There is an option that involves a combination of OPENROWSET and XML processing, but it requires that the files reside in the server's file system.

I needed to load into a table a series of log files generated by a Java web Application but had no access to upload them to the server, I had the logs in my local machine. I managed to upload data in a two step process that is not too cumbersome but its definitely too slow for a permanent solution.

I created a table that consists of two columns: an autonumeric primary key, and a varchar(max). I used the import data to upload the text files to the table so that each line in the file is a record in the table. The primary key coincidentally represents the line number. So I could write something like:

select LineNumber, TextLine from [LogFile] order by LineNumber

I then prepared another table with an structure that matched the records in my XML. My XML files had the particularity that each "value" tag was in its own text line, the opening and closing "record" tags where each on a separate line.

For example:

    <log>
    <record>
      <date>2018-07-27T09:54:20</date>
      <millis>1532706860250</millis>
      <sequence>13587</sequence>
      <logger>registroweb.ServReg</logger>
      <level>INFO</level>
      <class>somepackage.someclass</class>
      <method>methodname</method>
      <thread>11153</thread>
      <message>some very long text</message>
      <param>another long text</param>
    </record>
   ...
   </log>

This would mean that I could select all records where text_line = '<log>' would give me all opening record tags, but most importantly, self joining the table with t2.line_number = t1.line_number + 1 would always give me the line containing the date tag, line_number+2 would give millis, and so on.

So with the following query I was able to transform the flat linear table into a proper table:

insert into LogFileProcessed(
    [date],
    [millis],
    [sequence],
    [logger] ,
    [level]  ,
    [class]  ,
    [method] ,
    [thread] ,
    [message],
    [param]
)
select
    --record.TextLine, 
    convert(datetime, replace(replace(ltrim(dte.TextLine), '<date>',     ''), '</date>',     ''), 126) [date],
    convert(bigint,   replace(replace(ltrim(mls.TextLine), '<millis>',   ''), '</millis>',   '')) [millis],
    convert(bigint,   replace(replace(ltrim(seq.TextLine), '<sequence>', ''), '</sequence>', '')) [sequence],
    replace(replace(ltrim(logr.TextLine),   '<logger>',   ''), '</logger>',   '') [logger],
    replace(replace(ltrim(lvl.TextLine),    '<level>',    ''), '</level>',    '') [level],
    replace(replace(ltrim(cls.TextLine),    '<class>',    ''), '</class>',    '') [class],
    replace(replace(ltrim(mtd.TextLine),    '<method>',   ''), '</method>',   '') [method],
    replace(replace(ltrim(trd.TextLine),    '<thread>',   ''), '</thread>',   '') [thread],
    replace(replace(ltrim(msg.TextLine),    '<message>',  ''), '</message>',  '') [message],
    replace(replace(ltrim(prm.TextLine),    '<param>',    ''), '</param>',    '') [param]  
from LogFile record
left join LogFile dte    on dte.LineNumber    = record.LineNumber+1
left join LogFile mls    on mls.LineNumber    = record.LineNumber+2
left join LogFile seq    on seq.LineNumber    = record.LineNumber+3
left join LogFile logr   on logr.LineNumber   = record.LineNumber+4
left join LogFile lvl    on lvl.LineNumber    = record.LineNumber+5
left join LogFile cls    on cls.LineNumber    = record.LineNumber+6
left join LogFile mtd    on mtd.LineNumber    = record.LineNumber+7
left join LogFile trd    on trd.LineNumber    = record.LineNumber+8
left join LogFile msg    on msg.LineNumber    = record.LineNumber+9
left join LogFile prm    on prm.LineNumber    = record.LineNumber+10 and prm.TextLine <> '</record>' -- param is actually the only tag that is optional and some times is not present in the record.
where record.TextLine = '<record>'
order by 1, 2

Given the particular restrictions I have at the time and the structure of the files, this worked good enough for a one time task, allowing me to perform regular queries on the data without having to repeatedly run XML parsing or processing code.

Answer 2

As far as I know, you cannot do this by using a the import export wizard. Assuming you want the data to wind up all relational rather than as XML datatype, you'll need to create the table and use sp_xml_preparedocument and OPENXML.

See How to use OPENXML to load XML data into existing SQL Table?