SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 bulk insert xml files
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swoonef
Starting Member

4 Posts

Posted - 12/13/2013 :  14:27:27  Show Profile  Reply with Quote
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

Edited by - swoonef on 02/26/2014 21:12:32

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/14/2013 :  00:18:07  Show Profile  Reply with Quote
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
Go to Top of Page

swoonef
Starting Member

4 Posts

Posted - 01/16/2014 :  20:00:10  Show Profile  Reply with Quote
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>
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/17/2014 :  01:53:42  Show Profile  Reply with Quote
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
Go to Top of Page

swoonef
Starting Member

4 Posts

Posted - 02/26/2014 :  21:11:57  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/27/2014 :  02:34:23  Show Profile  Reply with Quote
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
Go to Top of Page

swoonef
Starting Member

4 Posts

Posted - 02/27/2014 :  08:33:44  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/27/2014 :  10:27:48  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000