Read CDATA from xml using LINQ not returning expected result

Question

I am trying to read the "query" element value (which is defined as CDATA) within an xml file using LINQ, however, when I run the application I am being returned the following value:

System.Linq.Enumerable+WhereSelectEnumerableIterator`2[System.Xml.Linq.XElement,System.String]

I am confused as to what I am missing to get the CDATA value.

The XML document looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<dbprocedurestruct>
  <variables id="vars">
    <variable id="ooid">
      <name>OrigOrgID</name>
      <pseudo>Original Organization ID</pseudo>
      <type>System.Int32</type>
      <visible>true</visible>
    </variable>
    <variable id="noid">
      <name>NewOrgID</name>
      <pseudo>New Organization(s) ID</pseudo>
      <type>System.Int32</type>
      <visible>true</visible>
    </variable>
    <variable id="nssid">
      <name>NewSysSubID</name>
      <pseudo>New System Subscription ID</pseudo>
      <type>System.Int32</type>
      <visible>false</visible>
    </variable>
  </variables>
  <scripts>
    <script name="createscript">
      <query>
        <![CDATA[
        SELECT 
        SystemSubscriptionID, 
        'INSERT INTO dbo.Subscriptions 
        (
        SubscriptionID, 
        SystemSubscriptionTierID, 
        IsCustomized, 
        SubscriptionPrice, 
        DownloadPrice, 
        PlansPrice, 
        SpecsPrice, 
        SubscriptionStartDate, 
        SubscriptionDuration, 
        AutoRenewSubscription, 
        IsActive, 
        SubscriptionStatus, 
        CreatedBy, 
        CreatedOn, 
        LastEditBy, 
        LastEditOn)
        VALUES (' + 
        CAST(SubscriptionID AS VARCHAR(50)) + ',' + 
        CAST(SystemSubscriptionTierID AS VARCHAR(50)) + ',' +
        CAST(IsCustomized AS VARCHAR(50)) + ',' + 
        CAST(SubscriptionPrice AS VARCHAR(50)) + ',' + 
        CAST(DownloadPrice AS VARCHAR(50)) + ',' + 
        CAST(PlansPrice AS VARCHAR(50)) + ',' + 
        CAST(SpecsPrice AS VARCHAR(50)) + ',''' + 
        CAST(GetDate() AS VARCHAR(50)) + ''',' + 
        CAST(SubscriptionDuration AS VARCHAR(50)) + ',' + 
        CAST(AutoRenewSubscription AS VARCHAR(50)) + ',' + 
        CAST(IsActive AS VARCHAR(50)) + ',' + 
        CAST(SubscriptionStatus AS VARCHAR(50)) + ', 2, ''' + 
        CAST(GetDate() AS VARCHAR(50)) + ''', 2, ''' + 
        CAST(GetDate() AS VARCHAR(50)) + ''') '  
        FROM Subscriptions WHERE SystemSubscriptionID IN 
        (SELECT SystemSubscriptionID FROM SubscriptionMappings WHERE ReferenceID = {0});
        SELECT IDENT_CURRENT('Subscriptions')
        ]]>
      </query>
    </script>
    <script name="mappingscript">
      <query>
        <![CDATA[
        INSERT INTO SubscriptionMappings 
        (SystemSubscriptionID,
        ReferenceID,
        SubscriptionTypeID,
        SystemId,
        IsActive,
        CreatedBy) 
        SELECT 
        {0},
        {1},
        CAST(SubscriptionTypeID AS VARCHAR(50)),
        2,
        CAST(IsActive AS VARCHAR(50)),
        2 FROM SubscriptionMappings 
        WHERE ReferenceID = {2} 
        AND 
        SystemSubscriptionID = {3}
        ]]>
      </query>
    </script>
  </scripts>
</dbprocedurestruct>

Here is my snippet of code that reads the xml file:

    SQLConnectivity _sc = new SQLConnectivity();
    DataTable dt = new DataTable();
    string xmlitem = (from scripts in xfile.Descendants("scripts") where scripts.Element("script").Value == "createscript" select scripts.Element("script").Element("query").Value).ToString();
    //String tempquery = xmlitem.Elements("query").ToString();
    String finalquery = String.Format(xmlitem, _ooid);

    _sc.RunExecuteSQL(finalquery, ref dt);
    return dt;

Nathan Baulch · Accepted Answer · 2013-02-27 06:36:28Z

3

The result of the query is an IEnumerable instance. If you want a single string result then you'll need to use either FirstOrDefault or SingleOrDefault. Also, you need to use the "name" attribute when searching for the designated "createscript" element.

var sql = (from scripts in xfile.Root.Elements("scripts")
           from script in scripts.Elements("script")
           let name = script.Attribute("name")
           where name != null && name.Value == "createscript"
           from query in script.Elements("query")
           select query.Value).FirstOrDefault();

Edit: Leading and trailing carriage returns and indentation can be stripped from the SQL with a combination of string.Trim and Regex.Replace.

var cleanSql = Regex.Replace(sql.Trim(), @"^\s+", "", RegexOptions.Multiline);

edited Feb 27, 2013 at 6:36

answered Feb 27, 2013 at 4:08

Nathan Baulch

20.9k5 gold badges56 silver badges59 bronze badges

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

2 Comments

mattgcon Over a year ago

Thank you Nathan, that worked beautiful. I just have to figure out how to get rid of the \n character now.

Nathan Baulch Over a year ago

No worries, I've added a suggestion for cleaning the SQL and further improved the LINQ query a little.

Collectives™ on Stack Overflow

Read CDATA from xml using LINQ not returning expected result

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related