bulk insert xml files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

bulk insert xml files

Author

Topic

swoonef
Starting Member

4 Posts

Posted - 2013-12-13 : 14:27:27

Hello everyone. I have multiple xml files in a directory that i would like to insert into tables. I can perform the action just fine with 1 file...but how do I add all the files in the directory into the same tables...or keep track of the files in that directory and if a new one is added it will be uploaded to the database. Thanks in advance for the help and hopefully you guys understand what i am trying to do. I have attached the script I am using to upload 1 file...

Thanks!

declare @XMLdocument xml
declare @doc_handle int

create table XMLfile(surveyXML xml)

bulk insert XMLfile
from 'C:\sample2.xml'
with ( rowterminator = ''' + char(10) + ''' )

select @XMLdocument = surveyXML from XMLfile

drop table XMLfile

exec sp_xml_preparedocument @doc_handle output, @XMLdocument

--select question map data
select *
into survey_question_map
from openxml(@doc_handle, 'DATA_EXPORT/HEADER/QUESTION_MAP/QUESTION', 2)
with (
[SERVICE] char(2) 'SERVICE',
VARNAME varchar(50) 'VARNAME',
QUESTION_TEXT varchar(8000) 'QUESTION_TEXT'
)

--select the patient level data with survery ID
select *
into survey_patient
from openxml(@doc_handle, 'DATA_EXPORT/PATIENTLEVELDATA', 2)
with (
SURVEY_ID int 'SURVEY_ID',
CLIENT_ID int 'CLIENT_ID',
[SERVICE] char(2) 'SERVICE',
RECDATE datetime 'RECDATE',
DISDATE datetime 'DISDATE'
)

--select the analysis data with survey ID
select *
into survey_analysis
from openxml(@doc_handle, 'DATA_EXPORT/PATIENTLEVELDATA/ANALYSIS/RESPONSE', 2)
with (
SURVEY_ID int '../../SURVEY_ID',
VARNAME varchar(50) 'VARNAME',
[VALUE] varchar(50) 'VALUE'
)

--select the demographics data with survey ID
select *
into survey_demographics
from openxml(@doc_handle, 'DATA_EXPORT/PATIENTLEVELDATA/DEMOGRAPHICS/RESPONSE', 2)
with (
SURVEY_ID int '../../SURVEY_ID',
VARNAME varchar(50) 'VARNAME',
[VALUE] varchar(50) 'VALUE'
)

--select the HCAHPS data with survey ID
select *
into survey_HCAHPS
from openxml(@doc_handle, 'DATA_EXPORT/PATIENTLEVELDATA/HCAHPS/RESPONSE', 2)
with (
SURVEY_ID int '../../SURVEY_ID',
VARNAME varchar(50) 'VARNAME',
[VALUE] varchar(50) 'VALUE'
)

--select the comments data with survey ID
select *
into survey_comments
from openxml(@doc_handle, 'DATA_EXPORT/PATIENTLEVELDATA/COMMENTS/RESPONSE', 2)
with (
SURVEY_ID int '../../SURVEY_ID',
VARNAME varchar(50) 'VARNAME',
SENTIMENT varchar(50) 'SENTIMENT',
[VALUE] varchar(8000) 'VALUE'
)

exec sp_xml_removedocument @doc_handle

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-14 : 00:18:07

you need to use a tool like SSIS for this. It has ForEachLoop container with file enumerator whch can be used to iterate over files. Then use data flow task with XML Source and OLEDB destination to dump xml data to staging table. Then use a execute sql task to call a,procedure which will insert data to your tables from staging table

see examples for loop here

http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

swoonef
Starting Member

4 Posts

Posted - 2014-01-16 : 20:00:10

i receive the following error when using the XML Source

Error at Data Flow Task [XML Source[1]]: The XML Source Adapter does not support mixed content model on Complex Types. This is the xsd file that it generates. I am not sure what i need to do with these files or how to edit them and make them import...as you can see I have the query to import one file but I am constantly adding new xml files into the same folder and i need to add this information to the same tables... I am not very familiar with importing this...thanks in advance for any help

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="DATA_EXPORT">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="HEADER">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="RECDATE">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="START" type="xs:date" />
<xs:element minOccurs="0" name="END" type="xs:date" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="SURVEY_COUNT" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="QUESTION_MAP">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="QUESTION">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="SERVICE" type="xs:string" />
<xs:element minOccurs="0" name="VARNAME" type="xs:string" />
<xs:element minOccurs="0" name="QUESTION_TEXT" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="PATIENTLEVELDATA">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="SURVEY_ID" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="CLIENT_ID" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="SERVICE" type="xs:string" />
<xs:element minOccurs="0" name="RECDATE" type="xs:date" />
<xs:element minOccurs="0" name="DISDATE" type="xs:date" />
<xs:element minOccurs="0" name="ANALYSIS">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="RESPONSE">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="VARNAME" type="xs:string" />
<xs:element minOccurs="0" name="VALUE" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="DEMOGRAPHICS">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="RESPONSE">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="VARNAME" type="xs:string" />
<xs:element minOccurs="0" name="VALUE" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="HCAHPS" type="xs:string" />
<xs:element minOccurs="0" name="COMMENTS">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="RESPONSE">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="VARNAME" type="xs:string" />
<xs:element minOccurs="0" name="SENTIMENT" type="xs:string" />
<xs:element minOccurs="0" name="VALUE" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-17 : 01:53:42

Ok in that case what you can do is use an Execute SQL task inside the loop and dump the xml files as is into a staging table using OPENROWSET BULK option

see
http://technet.microsoft.com/en-us/library/ms191184.aspx

Then have another Execute SQL task outside loop to process the XML files from table and shred data out using xml functions like nodes(), value etc
see
http://visakhm.blogspot.in/2012/10/shred-data-as-well-as-metadata-from-xml.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

swoonef
Starting Member

4 Posts

Posted - 2014-02-26 : 21:11:57

Okay I think I give up. I was able to get a few things to work but it's just not happening. I think plan b will be to modify the existing code I posted above to create table and import 1 file and try to have it append the tables and I will just run it for each file. Do you guys think that would be feasible? I really don't have that many files... Any help would be greatly appreciated. Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-27 : 02:34:23

quote:
Originally posted by swoonef

Okay I think I give up. I was able to get a few things to work but it's just not happening. I think plan b will be to modify the existing code I posted above to create table and import 1 file and try to have it append the tables and I will just run it for each file. Do you guys think that would be feasible? I really don't have that many files... Any help would be greatly appreciated. Thanks again

Why what was the issue?
Did you get some error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

swoonef
Starting Member

4 Posts

Posted - 2014-02-27 : 08:33:44

Well I used a Data Flow task with an XML source and ADO destinations. I did not bother with the loop just trying to get the variables in the right place. It works. But i think my XML is complex to i get a lot of tables i need to map. THe structure did not look anything like the bulk insert I had to begin with.
So...
I changed a few things around...

insert into survey_question_map
select *
from openxml(@doc_handle, 'DATA_EXPORT/HEADER/QUESTION_MAP/QUESTION', 2)
with (
[SERVICE] char(2) 'SERVICE',
VARNAME varchar(50) 'VARNAME',
QUESTION_TEXT varchar(8000) 'QUESTION_TEXT'
)

And i will just change the file name under

bulk insert XMLfile
from 'C:\sample2.xml'
with ( rowterminator = ''' + char(10) + ''' )

This will give me exactly the fields and tables i need.
I do not have many xml files...maybe 3-5/month I just have about 50 to load up front.

I will keep at it but for now i think i will go with what works...

I do appreciate all your help. Compared to where i was a few weeks ago i learned a lot. Can you offer any assistance in terms of cycling through these files? or creating a list?

Thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-27 : 10:27:48

quote:
Originally posted by swoonef

Well I used a Data Flow task with an XML source and ADO destinations. I did not bother with the loop just trying to get the variables in the right place. It works. But i think my XML is complex to i get a lot of tables i need to map. THe structure did not look anything like the bulk insert I had to begin with.
So...
I changed a few things around...

insert into survey_question_map
select *
from openxml(@doc_handle, 'DATA_EXPORT/HEADER/QUESTION_MAP/QUESTION', 2)
with (
[SERVICE] char(2) 'SERVICE',
VARNAME varchar(50) 'VARNAME',
QUESTION_TEXT varchar(8000) 'QUESTION_TEXT'
)

And i will just change the file name under

bulk insert XMLfile
from 'C:\sample2.xml'
with ( rowterminator = ''' + char(10) + ''' )

This will give me exactly the fields and tables i need.
I do not have many xml files...maybe 3-5/month I just have about 50 to load up front.

I will keep at it but for now i think i will go with what works...

I do appreciate all your help. Compared to where i was a few weeks ago i learned a lot. Can you offer any assistance in terms of cycling through these files? or creating a list?

Thank you very much!

Why not write the cycling part logic alone in SSIS using ForEachLoop
Then inside Loop add ExecuetSQLTAsk with above code part. Just replace static file path with variable which you use inside loop to get filename for each iteration and it should work fine for all files you've.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources