Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rudba
Constraint Violating Yak Guru
415 Posts |
 Posted - 2009-03-09 : 12:47:31
|
| How do we import data from xml file?Is it possible to import data from xml to sql using BULK?I have a this table on database:CREATE TABLE [dbo].[tblXmlDoc]( [ID] [int] NOT NULL, [Date] [datetime] NULL, [FFN] [varchar](50) NULL, [FLN] [varchar](50) NULL, [FEmail] [varchar](50) NULL, [TFN] [varchar](50) NULL, [TLN] [varchar](50) NULL, [TEmail] [varchar](50) NULL, [CFN] [varchar](50) NULL, [CLN] [varchar](50) NULL, [CEmail] [varchar](50) NULL) ON [PRIMARY]And i have xml file on this format.Note: Some record has multiple [To], [CC], [BCC] fileds and some does not have. Look at the following xml file.<?xml version="1.0" encoding="UTF-8" standalone="no"?><!-- Data provided by XYZ Comp. --><Doc><Version>MXML 1.6</Version><Msg> <ID>1</ID> <Date>2009-03-02-15.30.04.000000</Date> <Sender> <Info> <FN>Abc</FN> <LN>XYZ</LN> <From>abc@abc.com</From> </Info> </Sender> <Recipient = "To"> <Info> <FN>XX</FN> <LN>ZZz</LN> <To>kkk@kkk.com</To> </Info> </Recipient> <Recipient = "To"> <Info> <FN>DD</FN> <LN>DE</LN> <To>DD@kkk.com</To> </Info> </Recipient> <Recipient = "CC"> <Info> <FN>CC</FN> <LN>DD</LN> <Cc>aa@kkk.com</To> </Info> </Recipient> <Recipient = "CC"> <Info> <FN>Dg</FN> <LN>eE</LN> <Cc>ac@kkk.com</To> </Info> </Recipient></Msg><Msg> <ID>2</ID> <Date>2004-02-09-18.20.05.000000</Date> <Sender> <Info> <FN>oo</FN> <LN>ss</LN> <From>adddc@abc.com</From> </Info> </Sender> <Recipient = "To"> <Info> <FN>ddff</FN> <LN>gh</LN> <To>ffg@kkk.com</To> </Info> </Recipient> <Recipient = "CC"> <Info> <FN>fff</FN> <LN>gg</LN> <Cc>ahff@kkk.com</To> </Info> </Recipient> </Msg></Doc> |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-09 : 13:10:19
|
| Try like this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117490 |
 |
|
|
rudba
Constraint Violating Yak Guru
415 Posts |
Posted - 2009-03-09 : 14:09:24
|
| Thankx, i checked the above link. I have a data in xml more than 0.5 millions lines in a single xml, so is it possible to setDECLARE @doc varchar(max) SET @doc = all xml lines? (i mean path)Is not there othere way to import xml files? |
|
|
|
rudba
Constraint Violating Yak Guru
415 Posts |
Posted - 2009-03-09 : 14:41:17
|
| I tried this way:But it won't work. And also problems if there are multiples <Recipient = "To"> or <Recipient = "CC">CREATE TABLE [dbo].[tblXmlDoc]([ID] [int] NOT NULL,[Date] [datetime] NULL,[FFN] [varchar](50) NULL,[FLN] [varchar](50) NULL,[FEmail] [varchar](50) NULL,[TFN] [varchar](50) NULL,[TLN] [varchar](50) NULL,[TEmail] [varchar](50) NULL,[CFN] [varchar](50) NULL,[CLN] [varchar](50) NULL,[CEmail] [varchar](50) NULL) ON [PRIMARY]GODECLARE @xmlFileName VARCHAR(max)SELECT @xmlFileName = 'D:\Test.xml'EXEC('INSERT INTO tblXmlDoc(xmlFileName, ID,[Date],FFN,FLN,FEmail,TFN,TLN,TEmail,CFN,CLN,CEmail)SELECT ''' + @xmlFileName + ''', ID,[Date],FN,LN,From,FN,LN,To,FN,LN,CC FROM( SELECT * FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)')GOSELECT * FROM tblXmlDocBut this is work:here is xml file:<Products> <Product> <ID>1</ID> <Desc>Book</Desc> </Product> <Product> <ID>2</ID> <Desc>DVD</Desc> </Product> <Product> <ID>3</ID> <Desc>Video</Desc> </Product></Products>here is sql script:CREATE TABLE #XmlProduct( ID INT PRIMARY KEY, product_desc VARCHAR(30));goINSERT INTO #XmlProduct (ID, product_desc) SELECT X.product.query('ID').value('.', 'INT'), X.product.query('Desc').value('.', 'VARCHAR(30)')FROM ( SELECT CAST(x AS XML)FROM OPENROWSET( BULK 'D:\Products.xml', SINGLE_BLOB) AS T(x) ) AS T(x)CROSS APPLY x.nodes('Products/Product') AS X(product);select * from #XmlProductdrop table #XmlProduct |
|
|
|
|
|
|
|
|
How to import xml data into SQL Database?