Parsing XML data into SQL Server

timeware · March 1st, 2010, 08:03 AM

Hi,

I need to take an xml file, parse the data and invport it onto SQL server.

I'm not an expert on xml and normally I have no problems with this, but the xml file I have been presented with is in the format below and has a schema defined.

Code:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
 xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
 xmlns:rs='urn:schemas-microsoft-com:rowset'
 xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
 <s:ElementType name='row' content='eltOnly' rs:updatable='true'>
  <s:AttributeType name='Select' rs:number='1' rs:write='true'>
   <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='5'
    rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
  </s:AttributeType>
  <s:AttributeType name='UI' rs:number='2' rs:write='true'>
   <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='1'
    rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
  </s:AttributeType>
  <s:AttributeType name='No' rs:number='3' rs:write='true'>
   <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10'
    rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
  </s:AttributeType>
  <s:AttributeType name='ProductID' rs:number='4' rs:write='true'>
   <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10'
    rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
  </s:AttributeType>
  <s:AttributeType name='Distribution' rs:number='5' rs:write='true'>
   <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='50'
    rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
  </s:AttributeType>
   <s:extends type='rs:rowbase'/>
 </s:ElementType>
</s:Schema>
<rs:data>
 <rs:insert>
  <z:row UI=' ' No='100 D     ' ProductID='123456    '
    Distribution='Web                                               '/>
  <z:row UI=' ' No='501 D     ' ProductID='123457    '
    Distribution='Web                                               '/>
  <z:row UI=' ' No='1500 D    ' ProductID='123458    '
    Distribution='Tel                                               '/>
  <z:row UI=' ' No='1900 D    ' ProductID='123459    '
    Distribution='Tel                                               '/>
 </rs:insert>
</rs:data>
</xml>

I have cut down the data for your convenience (there are a lot more fields in eah row and a lot more lines)

What I need to know is how do I found out what is in each row for the fields 'No', 'ProductID' and 'Distribution' when I read in the data.
I can push the data into sql once I know the what it is, I just can' t work out how to read the fields themselves.

Thanks in advance.