Hi,

I need a little help importing an XML file into sqlserver 2008. I followed this example

http://www.sql-server-performance.com/articles/dba/Loading_XML_data_into_SQL_Server_2008_p1.aspx

(make sure you goto the rest of the pages in the article!) , generated the schema etc., but I can't get the import

to do what I need. I was able to add the DataConversion on my own but I can't seem to figure out how to get both ID

fields in the middle table.
I made a simplified version. For this example I have 3 tables. Books, BookAuthors, Authors.

Books Table (bookID, bookTitle, bookNumPages)
BookAuthors Table (bookID, authorID)
Authors (authorID, authorName)

After the Import the tables should look like this
Books
79, Some Good Book, 300
112, Another Book, 350
BookAuthors
79, 200
79, 205
79, 215
112, 200
Authors
200, First Author
205, Second Author
215, Third Author

The Problem I'm having is getting the BookID and AuthorID into the BookAuthors Table. I'm just wondering if there

is an easy way to set this up to Import or if I'm going to have to code it line by line.

<?xml version="1.0" encoding="UTF-8"?>
<result>
    <books>
        <book>
            <bookID>79</bookID>
            <bookTitle>Some Good Book</bookTitle>
            <bookNumPages>300</bookNumPages>
            <authors>
                <author>
                    <authorID>200</authorID>
                </author>
                <author>
                    <authorID>205</authorID>
                </author>
                <author>
                    <authorID>215</authorID>
                </author>
            </authors>
        </book>
        <book>
            <bookID>112</bookID>
            <bookTitle>Another Book</bookTitle>
            <bookNumPages>350</bookNumPages>
            <authors>
                <author>
                    <authorID>200</authorID>
                </author>
            </authors>
        </book>
    </books>
    <authors>
        <author>
            <authorID>200</authorID>
            <authorName>First Author</authorName>
        </author>
        <author>
            <authorID>205</authorID>
            <authorName>Second Author</authorName>
        </author>
        <author>
            <authorID>215</authorID>
            <authorName>Third Author</authorName>
        </author>
    </authors>
</result>
Re: Importing xml into sql server 2008 80 80

4 days and no answers so I just did it myself and ending up making a blog incase someone else needs to do the same thing. You can find the solution here

http://dvprez.blogspot.com/2009/10/importing-xml-into-sql-server-2008.html

commented: Thank you for sharing solution. PS: Not all questions get answer, that is the fait of forum entries... +11
Re: Importing xml into sql server 2008 80 80

hw do i import schema in .xml format to sql 2008 server?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.