0

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>
2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by l.geetha86
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.