SQLTeam.com | Weblogs | Forums

Microsoft Excel XML processing

tsql

#1

I have a document that is in machine generated Microsoft Office Excel XML format that I have imported into SQL Server 2012
After stripping out the multiple namespaces and getting rid of all the column formatting, I'm left with something like the structure below. The problem I'm having is the Index attributes on the cells which infers that there are blank values between. The sample row below should actually look like ClientID,NULL,NULL,City,State,Zip,NULL,LocationName. The NULL values are throwing off my columns when I import into SQL Server and try to split the fields into their proper columns. So I'm wondering if there's any way to query the Index attribute of the cell and determine the position within the row and match it to the actual position in TSQL, or if I should pre-process the file with a script task to insert the blank pairs.

<Table>
  <Row>
     <Cell>
        <Data>ClientID</Data>
     </Cell>
     <Cell Index=4>
        <Data>City</Data>
     </Cell>
     <Cell>
        <Data>State</Data>
     </Cell>
     <Cell>
        <Data>Zip</Data>
     </Cell>
     <Cell Index=8>
        <Data>LocationName</Data>
     </Cell>
   </Row>
<Table>

#2

I ended up adding a script task that inserted blank values into the file to properly index the file without having to try to get at that index attribute via SQL.