Schema to convert XML into relational database

[ma701ss]

Hi,

I have the following sample code:

Code: Select all

<?xml version="1.0" ?>

<STOREITEMS>

  <CREATED>

    <CATEGORY id='181' name='Category name'>

      <PRODUCT ITEM='7451'>

        <WEIGHT>0.50</WEIGHT>

        <NAME>Product name</NAME>

        <MODEL>Product model</MODEL>

        <PRICE>£5.99</PRICE>

        <IMAGE>image.jpg</IMAGE>

        <DESCRIPTION>Product description.</DESCRIPTION>

      </PRODUCT>

      <PRODUCT ITEM='7452'>

        <WEIGHT>0.60</WEIGHT>

        <NAME>Product 2 name</NAME>

        <MODEL>Product 2 model</MODEL>

        <PRICE>£6.99</PRICE>

        <IMAGE>image2.jpg</IMAGE>

        <DESCRIPTION>Product 2 description.</DESCRIPTION>

      </PRODUCT>

    </CATEGORY>

  </CREATED>

</STOREITEMS>

I created a schema for this (reverse engineered) using an online editor. The trouble is the data in the XML file does not convert correctly into a relational database format. For example I need the category ID to be on each "product" row, and product item refers to the unique ID for each product but it's an attribute and it doesn't get recognised as such. There may be many products within the same category ID. I need to have the following columns in my table, and for the XML to be converted into such a format that I can import into SQL Server:

Product Item (Primary Key)
Category ID
Weight
Product Name
Model
Price
Image
Description

Is it a case of re-writing the XML schema to do this?

Thanks