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>