How to query this XML document in C#.NET 3.5

Question

I have an XML document of the form:

<metadata>
      <item name="Name" type="xs:string" length="102"/>
      <item name="Email" type="xs:string" length="202"/>
</metadata>
<data>
    <row>
        <value>Daniel</value>
        <value>[email protected]</value>
    </row>
    <row>
        <value>George</value>
        <value>[email protected]</value>             
    </row>
</data>

The ordering and number of columns may change so it is not sufficient to assume //row/value[1]/text() always holds 'Name'.

What is the best way to query the document using the metadata names to pull the right values?

I am querying the document in C#.NET3.5 so can use XDocument, XPath etc... whichever is the best for the job.

This is NOT a valid, well-formed XML document - the first ground rule is: one and only one root element - you don't have that here.... — marc_s
– marc_s, Commented Dec 9, 2009 at 14:55
It does have a root element - I just neglected to add it into the example. — djskinner
– djskinner, Commented Dec 9, 2009 at 15:13

Scoregraphic · Accepted Answer · 2009-12-09 14:31:53Z

1

Personally, I would transform this input (is it plist?) into a "real" XML document where the tags have better names (i.e. the metadata description). You can achieve this by using xsl transformation and a selfwritten stylesheet (can assist if needed).

After that, you would have a structure like:

<data>
  <row>
    <Name>Daniel</Name>
    <Email>[email protected]</Email>
  </row>
  <row>
    <Name>George</Name>
    <Email>[email protected]</Email>
  </row>
</data>

Now then it's easy to address the nodes using /data/row/Name and selecting them with an XPathNavigator

answered Dec 9, 2009 at 14:31

Scoregraphic

7,2605 gold badges47 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

djskinner Over a year ago

It's coming out of a Cognos reporting engine and I have no control over the format. That might be the way forward but I would still need to understand how to query to original document in XSL. I'll explore this option further.

Scoregraphic Over a year ago

I'll provide a stylesheet after I got home (in about an hour) ;)

djskinner Over a year ago

I have had a first go at a stylesheet (see answer to my own question). It appears to work with some test data. Is this intermediary stylesheet the best way to approach it?

Scoregraphic Over a year ago

I don't know if it's the best way, but it's a way and easy to maintain. It would be also possible to deal with the metadata mapping in code (XmlReader, XmlDocument, etc), but I find it easier this way.

djskinner · Accepted Answer · 2009-12-09 15:57:09Z

1

This intermeidary stylesheet is my attempt in response to Scoregraphic's suggestion (I posted as an answer for readability):

<?xml version='1.0' ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<data>
    <xsl:for-each select="/doc/data/row">
    <row>
        <xsl:for-each select="./value">
        <xsl:variable name="cur" select='position()' />
        <xsl:element name="{/doc/metadata/item[$cur]/@name}">
            <xsl:value-of select="./text()" />
        </xsl:element>
        </xsl:for-each>
    </row>
    </xsl:for-each>
</data>
</xsl:template>
</xsl:stylesheet>

answered Dec 9, 2009 at 15:57

djskinner

8,1254 gold badges52 silver badges72 bronze badges

1 Comment

Scoregraphic Over a year ago

After a first look, this should work fine. Please check all the possible metadata-output tags, so you don't write illegal XML characters (in your example, this is no problem). I think of chars like &, <, >, etc.

Neil · Accepted Answer · 2009-12-09 14:28:49Z

0

This is similar to what you're doing, just a nice place to start

<cars xmlns="/carsSchema.xsd">
  <car age="5">
    <carId>1</carId>
    <brand>BMW</brand>
    <model>320i</model>
    <color paintType="metallic">Red</color>
  </car>

  <car age="2">
    <carId>2</carId>
    <brand>VW</brand>
    <model>Golf</model>
    <color paintType="matt">White</color>
  </car>
[...]
</cars>


XDocument xmlDoc = XDocument.Load(currentDir + "\\Cars.xml");
XNamespace ns = "/carSchema.xsd";

var carInfo1 = from car in xmlDoc.Descendants(ns + "car")
                   select (string)car.Element(ns + "brand") + ": " + 
                          (string)car.Element(ns + "model");

answered Dec 9, 2009 at 14:28

Neil

1,92217 silver badges24 bronze badges

Comments

Filburt · Accepted Answer · 2009-12-11 12:15:04Z

0

Let me guess: you're retrieving a xml-formatted cognos report?

I'm just about to deal with the same problem but i think cognos supports defining a schema for your report result, so you won't have to xsl-transform it.

...

I rummaged around a bit and found the following url parameter options in the offical cognos viewer documentation:

run.xslURL - Specifies the location of a XSL-stylesheet to apply to the report. Value for this param is a valid URI.

I did not try this yet, but i will need to transform a report result just like you.

edited Dec 11, 2009 at 12:15

answered Dec 9, 2009 at 14:39

Filburt

18.1k14 gold badges106 silver badges167 bronze badges

4 Comments

djskinner Over a year ago

Yes but it's the web API integrated into a SaaS product and I cannot see any way of configuring the output (not even the Excel output)

djskinner Over a year ago

It would be handy if I could do that with the above transform but I only have access to a very basic web-based query studio with no option to specify this.

djskinner Over a year ago

I use the same principle but perform the transfom outside of cognos

Filburt Over a year ago

I went to transform outside cognos too - your xsl sample was very useful +1.

Collectives™ on Stack Overflow

How to query this XML document in C#.NET 3.5

4 Answers 4

4 Comments

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related