Import XML

Hi All,

I know this is a question that has probably been asked a million times but I am trying to import quite a number of large xml files into SQL server 2005, I know that this is something that can now be done using SSIS but having only just started using it I really dont have any idea where to start, I have also looked at third party software to either import it direct into SQL Server or into a text file for later importing but not having much luck with that either, does anyone have any ideas how to go about this in a idiots guide to importing xml.

Thanks
nighttressAsked:
Who is Participating?
 
brejkCommented:
"Expert" is a very relative term but thanks :-)

Please provice an example of your XML.
0
 
brejkCommented:
There is also option like this:

INSERT INTO YourTable (YourXmlColumn)
SELECT * FROM OPENROWSET(BULK N'C:\YourXmlFile.xml', SINGLE_NCLOB) T

If XML file is not "unicoded" then use SINGLE_CLOB instead of SINGLE_NCLOB. If you are going to import multiple files then you may use SQLCMD tool and its variables:

1. Prepare a sql script like the one below (change the proper names and save it for example as C:\script.sql):

USE YourDatabase
GO

INSERT INTO dbo.YourTable(YourXmlColumn)
SELECT * FROM OPENROWSET(BULK $(variable), SINGLE_NCLOB) T

2. In command line go to the directory where you have your xml files and execute something like this:

for %i in (*.xml) do sqlcmd -E -S YourInstanceName -i C:\script.sql -v variable="'%~fi'"

The above will execute sql script for every xml file in the directory.

0
 
nighttressAuthor Commented:
Hi brejk,

Thanks again for a quick response, is there nothing that you are not an expert on :-)

I have done exactly as you have said and it seems to be getting there, the problem is when I ran the script it went to the correct directory but said that it could not open the xml file, I checked and the file is not open and I had no problem opening it with notepad?

Also once I have the data into the table how easy is it for me to query the data out of it, each xml file relates to one institutions and lists paragraphs and grades going down the page with a heading for each grade, what I would want to do in the query is list the data across the page, something like this

Company Name     Health&Safety            Time
SO AND SO           Good                          3

The data that comes under each of the headers could be a mix of things and these are just an example of headers, if it would be any easier I could post an example of the xml.

Thanks again
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
nighttressAuthor Commented:
In relation to the cannot open the file, I will tell you the scenario that I have, at the moment I am working from home over a vpn connection, I have put the file in a directory of C:\XML and I have run the script from the same direcotry.  I have admin privs on all folders and system both locally and remotely.

Here is an example of the xml, you will see part way through this sort of lay out

  <InspectionKeyData>
    <InspectionNumber>301690</InspectionNumber>
    <InspectionBeginningDate>2007-11-20</InspectionBeginningDate>
    <InspectionEndDate>2007-11-21</InspectionEndDate>
    <ReportingInspector>
      <InspectorName>Mr</InspectorName>
      <InspectorStatus>AI</InspectorStatus>
    </ReportingInspector>
    <InspectionCategory>s5</InspectionCategory>
    <DateOfPreviousInspectionSchool>2003-04-01</DateOfPreviousInspectionSchool>
    <AmendedReport>no</AmendedReport>
  </InspectionKeyData>

-----THEN THIS---------

<PositiveContribution>
          <SchoolGrade>1</SchoolGrade>
          <SixthFormGrade>9</SixthFormGrade>
        </PositiveContribution>

How I want to script the data out is something like this

These 2 lines are the table headers

InspectionNumber | InspectionBeginningDate |  InspectionEndDate |  Inspectorname  |
PositiveContribution  - School Grade  |   PositiveContribution - SixthFormGrade   |

There will be more headers to account for all the different grade areas

If this does not make sense please let me know.

I have converted it to a txt file as the system would not let me upload an xml file.

Thanks
IN301690.TXT
0
 
nighttressAuthor Commented:
Right I have got past that error, must have something to do with the permissions but I cant see why, anyway it works on the sql box which is where it would go in the end anyway......I now have an error that
says

Msg 9455, Level 16, State1, Server SERVERNAME, Line2
XML parasing: line1, character 2, illegal qualified name character

Does this make any sense now that you can see the xml file?

Thanks


0
 
nighttressAuthor Commented:
Sorry about this, I am nothing but persistent, I have now managed to get the script to load a number of xml files into the table which is brilliant, thanks for the help, can you explain in idiots terms :-) how I go about getting the data out in the format as I described earlier.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.