Hi All,

I'm developing an Excel add-in (using C#) which will call a web service to get a "data record" from the company's SQL Server 2014 database and write that data out onto a set of worksheets.  "Data record" is deliberately in quotes as you will see in a moment.  There is a bunch of ancillary information in the record (the usual date/time stamp, person who last edited the record, etc.).  However, the core of the data is held in an XML database field.  The XML breaks out into two child sections under the root: a "ProcessingDefaults" child section and a "DTDataMembers" child section.  The child sections are basically just tables of data.  I will put each child section on its own worksheet.

And yes, the backend developer did include an "XML Schema" as part of the "data record"; again held in an XML database field type. 

The 'core' data shows up on my end of the web service as simply a 'string' type.  The 'schema' is held in a custom data type (class) that is essentially a collection, the members of which basically define the characteristics of individual columns.

Now, I could traverse the XML and write data into individual cells on the sheets.  Ugly, but possible as a last resort.

What I'm looking for is a more elegant solution that leverages the XML facilities available in .NET and Excel. 

I looked at the documentation for XMLMapping and thought that ImportXML might work, but when I tried to invoke it in my code:

   Microsoft.Office.Interop.Excel.XmlMap myMap = new Microsoft.Office.Interop.Excel.XmlMap();

   response = myMap.ImportXML(XMLData)

I get an "Cannot create an instance of the abstract class or interface" error when defining 'myMap'.  (And yes, I have a Reference to 'Microsoft.Office.Interop.Excel'.  And yes, I have 'using Excel = Microsoft.Office.Interop.Excel; '.  And yes, I should also be able to write the code as 'Excel.XmlMap'.)  Going back and reading the MSDN documentation, I get the impression that XmlMap might be deprecated with v2013, but hey, what do I know?

A hack would be to write the XML to a temporary file and import it.  When I broached this idea to my boss, he had a decidedly negative response (something along the lines of "Let's do this right."; can't say I disagree with him.)

So, in lieu of tearing my hair out, I'm turning to you guys (guys being used in a unisex manner).  Anybody have any suggestions?  Linq?  Something else?

Here's some additional info:
The web service was originally targeted for a Silverlight control.  The idea for the add-in is to expand the options available to the user (not to mention that Silverlight is deprecated).
In theory, the database and web service could be modified.  In practice: no.  (And it really is a "No".)
Excel versions 2007 and later are the targets.  As a result, 4.0 is the target .Net version.
This is my first add-in.
I have the most basic understanding of XML.  After that, it's all a learning experience (which I'm willing -- just wanted to let you know that I have enough knowledge to be dangerous to myself).

I have included samples of the schema and the data XML.  These samples are based on the "real" versions.  However, in mocking them up, I might have made a minor error (or two ...); please view them as 'mostly' correct. 

As always, your help is greatly appreciated.

     Richard

<DTProcessingSchema>
  <ColumnDefinitions>
    <ColumnDefinition>
      <ColumnName>Source</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>0</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>SourceDC</ColumnName>
      <TypeName>AProcessingStyle</TypeName>
      <RelativeRank>1</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>Destination</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>2</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>DestinationDC</ColumnName>
      <TypeName>AProcessingStyle</TypeName>
      <RelativeRank>3</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>UCode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>4</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>AlternateCode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>5</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>DCode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>6</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>OCode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>7</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>PCode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>8</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>Name</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>9</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>ACode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>10</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>FACode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>11</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>OACode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>12</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>VCode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>13</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>BCode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>14</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>LR</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>15</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>LD</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>16</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>Comment</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>17</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>RCode</ColumnName>
      <TypeName>System.String</TypeName>
      <RelativeRank>18</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>X1</ColumnName>
      <TypeName>System.Decimal</TypeName>
      <RelativeRank>19</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>X2</ColumnName>
      <TypeName>System.Decimal</TypeName>
      <RelativeRank>20</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>X3</ColumnName>
      <TypeName>System.Decimal</TypeName>
      <RelativeRank>21</RelativeRank>
    </ColumnDefinition>
    <ColumnDefinition>
      <ColumnName>X4</ColumnName>
      <TypeName>System.Boolean</TypeName>
      <RelativeRank>22</RelativeRank>
    </ColumnDefinition>
  </ColumnDefinitions>
</DTProcessingSchema>

<DTProcessing>
  <ProcessingDefaults>
    <ProcessingDefault>
      <Code>1234-AB-CD-56-EFG-78</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Sample</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>102</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Sample</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>103</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Sample</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>104</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Sample</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>105</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Sample</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>201</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Review</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>202</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Review</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>203</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Review</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>204</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Review</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>205</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>Review</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>301</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>None</CodeType>
    </ProcessingDefault>
    <ProcessingDefault>
      <Code>302</Code>
      <ProcessingStyle>Normal</ProcessingStyle>
      <CodeType>None</CodeType>
    </ProcessingDefault>
  </ProcessingDefaults>
  <DTDataMembers>
    <DTData>
      <Source>1234-AB-CD-56-EFG-78</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>123456</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode>Z1234</UCode>
      <AlternateCode>XVYT</AlternateCode>
      <DCode>5678</DCode>
      <OCode>99990</OCode>
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode>01</VCode>
      <BCode>A</BCode>
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>102</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>102</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>103</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>103</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>104</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>104</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>105</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>105</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>201</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>201</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>202</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>202</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>203</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>203</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>204</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>204</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>205</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>205</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>301</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>301</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
    <DTData>
      <Source>302</Source>
      <SourceDC>Normal</SourceDC>
      <Destination>302</Destination>
      <DestinationDC>Normal</DestinationDC>
      <X1>0</X1>
      <X2>0</X2>
      <X3>0</X3>
      <X4>true</X4>
      <UCode />
      <AlternateCode />
      <DCode />
      <OCode />
      <PCode />
      <Name />
      <ACode />
      <FACode />
      <OACode />
      <VCode />
      <BCode />
      <LR />
      <LD />
      <Comment />
      <RCode />
    </DTData>
  </DTDataMembers>
</DTProcessing>

Hi RichardProfAct,   I suspect you need to persist the XML to a file and load that file into Excel to get this stuff going.    

Yeah, except my boss put the kibosh on that.  I understand his viewpoint.  I may not like it, but I understand it ...

Any other ideas?  Linq, somehow?

     Richard

Did you consider to use the WEBSERVICE function with FILTERXML that can extract the desired data from the XML using XPATH?

You can also consider to integrate Power Query into your solution. It has a powerful import capabilities from XML content directly from your web service.

• Edited by Gil RavivMicrosoft employee 10 hours 16 minutes ago

Did you consider to use the WEBSERVICE function with FILTERXML that can extract the desired data from the XML using XPATH?

You can also consider to integrate Power Query into your solution. It has a powerful import capabilities from XML content directly from your web service.

• Edited by Gil RavivMicrosoft employee Tuesday, March 17, 2015 9:09 PM

Hi Richard,

Please try the above suggestion first, and then this is the forum to discuss questions and feedback for Microsoft Excel, your issue is related to Excel development, I recommend you post the question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support

You mean there is an Excel forum for developers?  And that's different from the Excel forum for IT Professionals???  Typical Microsoft: there is an answer -- you just have to work to find it.

I know the above is sarcastic, and please understand that it is not directed at you, George.  In fact, please accept my very real thanks for pointing the Excel developers forum out.  I have not only bookmarked that forum, but several other Office developer forums as well. 

I'm not sure how I got here to this particular forum.  BUT, I know that when I went to choose a forum, this was the only relevant one listed.  Why was that?  I suspect it's because I somehow made my way to this site: TechNet.  Where you're directing me to is MSDN.  And the distinction to the non-Softie is ?????

I have similar beefs with Microsoft's educational efforts:  MVA vs. Channel 9 vs. whatever else.

Microsoft can assert all the nuanced distinctions it wants.  To outsiders, it's all just Microsoft.  Don't force us to become insiders. 

In some respects, the message from Microsoft is: do as I say, not as I do.  Microsoft makes me, the user, have a unified Microsoft account, but Microsoft itself doesn't present a unified entity behind that Microsoft account.

If you have any insider connections to Microsoft, please feel free to share this post.

In the meantime, may I suggest that someone add the developer forums to the pick list?  If that's not possible, perhaps you could modify the page to include a link to the Excel for Developers forum.  And/or have a drop-down box which lists additional useful resources.

But whatever, in the meantime, please accept my heartfelt thanks for getting me hooked into the Office developers forums.  It is very much appreciated!

     Richard

Gil,

Thanks for all the good suggestions.  Unfortunately, these require Excel 2013, and I need to reach back to Excel 2007.

The good news is that they are intriguing capabilities that I wasn't aware of.  Thanks to you, now I am.  Once I solve my immediate problem, I plan to delve into all the resources that I bookmarked during my quick research. 

Thanks for your help,

Richard

George, et al,

Per your recommendation, this has been re-posted at

https://social.msdn.microsoft.com/Forums/en-US/8a98b862-9bfe-42f5-a337-2e584cc7a200/import-xml-table-into-excel-from-web-service?forum=exceldev

Thanks again for all your help.

     Richard'

• Marked as answer by RichardProfAct 13 hours 56 minutes ago