• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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

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
tesla764
Asked:
tesla764
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
tesla764Author Commented:
Thanks. That was very helpful.
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now