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 XMLfiledrop table XMLfileexec sp_xml_preparedocument @doc_handle output, @XMLdocument--select question map dataselect * into survey_question_mapfrom 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 IDselect * into survey_patientfrom 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 IDselect *into survey_analysisfrom 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 IDselect *into survey_demographicsfrom 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 IDselect *into survey_HCAHPSfrom 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 IDselect *into survey_commentsfrom 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 tablesee examples for loop herehttp://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspxhttp://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 SourceError 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 |
|
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 MVPhttp://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_mapselect *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_mapselect *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 ForEachLoopThen 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|