XML import SQL Server 2005 Express

Question

XML import SQL Server 2005 Express

Nick Kernick

SSC Rookie

Points: 34
More actions
December 16, 2009 at 9:15 am

#74577

Im sorry about this basic question, however im banging my head, I cant find a simple answer to this question.
I have an XML and an XSD file, how do i import them into SQL server express 2005
I have tried ASP, VBscript, and get components errors
Surely this is basic stuff ?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Have you tried OpenRowset, using the Single_CLOB option?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Nick Kernick SSC Rookie Points: 34 More actions · Answer 2

Yes, however i think i got it wrong, as it imported the XSD to a single column in SQL , i thought it might create all the fields. Maybe im expecting too much here but it looks like it should be simple

GSquared SSC Guru Points: 260824 More actions · Answer 3

If you import the text of the XSD definition into a table, you could then use that to dynamically build a "Create XML Schema Collection" command. Look up the command in Books Online for the syntax.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Nick Kernick SSC Rookie Points: 34 More actions · Answer 4

Thanks for that. reading about SQL server and XML , they make it sound like it was designed for XML

I must be missing something when i thought it would be like importing a CSV file. How wrong was I

So i import the XSD, then use that to create a schema , then somehow use that to import the actual data. Wow , thought computers were meant to be becoming easier !!!!

Are there any working examples out there, all i see is masses of code ??

or is that where im going wrong, is it not as easy as i thought

thanks for your time

GSquared SSC Guru Points: 260824 More actions · Answer 5

Computers are becoming easier for end-users, not for DBAs. For us, it gets more complex all the time!

This site had a series of articles last year (I think it was last year) on using XML in SQL 2005. Go to the articles section and select XML as a tag, and you should get a pretty good introduction there.

I think http://www.simple-talk.com also has had some articles on XML in SQL. If Phil and Robyn had a workbench on it, that'll also be a good place to get real data.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon