How do I get then filter XML from a SQL Server 2000 DB?

Question

I have a table in SQL Server 2000 with a text field containing XML that I need to display on a C# ASP.NET 2.0 page. I need to retrieve the XML and then filter out a list of about 80 possible elements (or white list 20 possible elements to keep might be better).

I can pull the xml out of the DB and display it on my .aspx page, but I am not sure how to filter out any elements first.

Example XML

<Message> 
  <MessageNumber>
    1234
  </MessageNumber> 
  <MessageType>
    Auto Notice
  </MessageType> 
  <UPMessageNumber>
    5501
  </UPMessageNumber> 
  <MessageID>
    121223
  </MessageID> 
  <ResponseTo>
    654321
  </ResponseTo>
  <DateTime>
    2010-11-10 09:35:00
  </DateTime>
</Message>

In this case I will need to filter out the UPMessageNumber and MessageID before displaying it on the page.

Thanks for any ideas.

score 3 · Accepted Answer · edited May 23 '17 at 12:19

You can do this using XSLT to transform input XML into a modified output.

C# example here.

This step-by-step article shows you how to apply an Extensible Stylesheet Language (XSL) Transformation (XSLT) to an Extensible Markup Language (XML) document by using the XslTransform class to create a new XML document. XSL is an XML-based language that is designed to transform one XML document into another XML document or an XML document into any other structured document.

The previous question here addresses directly how to construct XSL for element stripping.

I think the XSL you need is:

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="xml"/>

  <xsl:template match="@*|node()" priority="10">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="UPMessageNumber | MessageID" priority="20">
  </xsl:template>

</xsl:stylesheet>

The first xsl:template copies all nodes by default. The second omits the named elements.

So my question on this would be how do I transform the XML when it is not a file? I have this as an XML object, but I am not finding where to add the reference to the xsl to the object. If it was a file, I could just add the "xml-stylesheet" tag. — InsertOldUserIDHere, Nov 11 '10 at 16:22
@zk - you can use `XslCompiledTransform.Transform` directly on an `XmlDocument` - http://msdn.microsoft.com/en-us/library/ms163434.aspx - let me know if you get stuck (or post a new question on this) — Steve Townsend, Nov 11 '10 at 16:26

score 1 · Answer 2 · answered Nov 10 '10 at 16:06

If you want to do it in a simplistic way, you could leverage the DataSet class to read your xml, and then use a Data View Filter.

    DataSet ds = new DataSet();
    ds.ReadXml(@"C:\so.xml");
    DataTable dt = ds.Tables[0];
    DataView dv = dt.DefaultView;
    dv.RowFilter = "Your Row Filter" //"MessageNumber<>4567" or construct a filter using //a helper
    //Bind dv to grid ??

Again once you have your data in a dataset you can do all kinds of things, like selective display of columns, I'm not sure if you wanted to filter the data from a query perspective or "hide" data from view with it still being present

fyi I read the xml from a file, I saw you're getting your data from a db, still you can get the underlying xml into a dataset fyi — Ta01, Nov 10 '10 at 16:09

How do I get then filter XML from a SQL Server 2000 DB?

2 Answers2