Schema to convert XML into relational database

This should cover W3C XML Schema, Relax NG and DTD related problems.
ma701ss
Posts: 1

Schema to convert XML into relational database

Mon Jan 19, 2015 6:33 pm

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

Return to “XML Schemas”

Who is online

Users browsing this forum: No registered users and 0 guests