MS SQL

MS SQL RSS

Importing xml into sql server 2008

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jul 2007

Posts: 4

Reputation: dvprez is an unknown quantity at this point

Solved Threads: 0

dvprez

Offline

Newbie Poster

Importing xml into sql server 2008

Oct 24th, 2009

Hi,

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

http://www.sql-server-performance.co...r_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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
<?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>
<?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>

•

Join Date: Jul 2007

Posts: 4

Reputation: dvprez is an unknown quantity at this point

Solved Threads: 0

dvprez

Offline

Newbie Poster

31 Days Ago

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/i...rver-2008.html