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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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
brejkCommented:
"Expert" is a very relative term but thanks :-)

Please provice an example of your XML.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
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


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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.