Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

load xml file into a database so that I can see the data that is in the XML file

Posted on 2014-10-17
2
Medium Priority
?
140 Views
Last Modified: 2014-10-17
Hi,
We have a very large xml file called GMEIIssuedFullFIle.xml

What would the SQL code be to...
load the file into a test database so that we can view the data that is in the xml file.

Thanks for your help in advance
0
Comment
Question by:tesla764
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 40387006
You could do that for instance like in the query below assuming you know the structure of your XML doc:

CREATE TABLE [dbo].XMLtable_load(
      [bip] [varchar](10) NULL,
      [Doc_TYPE] [varchar](50) NULL,
      [Doc] [text] NULL,
      [ID] [varchar](50) NULL,
      [rowOrder] [int] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


INSERT INTO XMLtable_load (bip,doc_type,doc,id,roworder)
 SELECT X.bip.query('bip').value('.', 'VARCHAR(10)'),      
 X.bip.query('Doc_TYPE').value('.', 'VARCHAR(50)'),
 X.bip.query('Doc').value('.', 'VARCHAR(8000)'),
 X.bip.query('@ID').value('.', 'VARCHAR(50)'),
 X.bip.query('@rowOrder').value('.', 'INT')
 FROM
 ( SELECT CAST(x AS XML)FROM OPENROWSET(     BULK 'C:\Downloads\BipDocs.xml', SINGLE_BLOB) AS T(x)     )
 AS T(x)CROSS APPLY x.nodes('Root/bip_Doc') AS X(bip);
0
 

Author Closing Comment

by:tesla764
ID: 40387529
Thanks. That was very helpful.
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Integration Management Part 2
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question