locked
Inert Master-Detail Xml records in database RRS feed

  • Question

  • User1903088995 posted

    Hi everyone,

    I am fairly new to this one. I have an xml document, that has multiple master-detail product records that I need to insert into multiple table in sql server accordingly. My xml looks like this,

    <?xml version="1.0" encoding="UTF-8"?>
    <products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    	<product xsi:type="ProductWithReviews" locale="en_US">
    		<pageid>595055003</pageid>
    		<name/>
    		<smallstarimagelocation>pwr/engine/images/stars_small.gif</smallstarimagelocation>
    		<largestarimagelocation>pwr/engine/images/stars.gif</largestarimagelocation>
    		<newestreviewdate>2010-07-05</newestreviewdate>
    		<oldestreviewdate>2010-04-22</oldestreviewdate>
    		<taggroup key="bestuses" name="Best Uses">
    			<tag isuseradded="true" count="5">Business casual</tag>
    			<tag isuseradded="true" count="4">CASUAL WEAR</tag>
    			<tag isuseradded="true" count="4">Cold weather</tag>
    			<tag isuseradded="true" count="5">Going out</tag>
    			<tag isuseradded="true" count="3">School</tag>
    			<tag isuseradded="true" count="3">Travel</tag>
    		</taggroup>
    		<taggroup key="cons" name="Cons">
    			<tag isuseradded="true" count="6">Poor arch support</tag>
    			<tag isuseradded="true" count="5">Poor cushioning</tag>
    			<tag isuseradded="true" count="3">Poor fit</tag>
    			<tag isuseradded="true" count="3">Stains easily</tag>
    			<tag isuseradded="true" count="5">Too stiff</tag>
    			<tag isuseradded="true" count="3">Uncomfortable</tag>
    			<tag isuseradded="true" count="2">Wears out quickly</tag>
    		</taggroup>
    		<reviews>
    			<fullreview>
    				<id>23551204</id>
    				<createddate>2010-06-05</createddate>
    				<helpfulvotes>1</helpfulvotes>
    				<nothelpfulvotes>0</nothelpfulvotes>
    				<taggroup key="pros" name="Pros">
    					<tag isuseradded="true" count="1">BREATHES WELL</tag>
    					<tag isuseradded="true" count="1">Good arch support</tag>
    					<tag isuseradded="false" count="1">Durable</tag>
    					<tag isuseradded="true" count="1">Comfortable</tag>
    				</taggroup>
    				<comments>Lorem ipsum dolor sit amet, ligula nulla pre tium</comments>
    			</fullreview>
    			<fullreview>
    				<id>23551204</id>
    				<createddate>2010-06-05</createddate>
    				<helpfulvotes>1</helpfulvotes>
    				<nothelpfulvotes>0</nothelpfulvotes>
    				<taggroup key="pros" name="Pros">
    					<tag isuseradded="true" count="1">BREATHES WELL</tag>
    					<tag isuseradded="true" count="1">Good arch support</tag>
    					<tag isuseradded="false" count="1">Durable</tag>
    					<tag isuseradded="true" count="1">Comfortable</tag>
    				</taggroup>
    				<comments>Lorem ipsum dolor sit amet, ligula nulla pre tium</comments>
    			</fullreview>
    		</reviews>
    	</product>
    </products>

    This is just one product record with multiple detail nodes. There can be several product records in this xml. What I want to do is, foreach product I need to insert data at parent and detail nodes in to multiple master detail tables. Dooes anyone knows the best way to do it. Is there a way I can do this using bulk insert? Currently I am trying to write sproc using sp_xml_preparedocument and OPENXML but I am not sure how will it work for multiple record insert without iterating through each parent node to grab the child nodes? Any help will be extremely helpful.

    Thanks.

     

     

    Friday, May 6, 2011 9:40 AM

Answers