[Steve]

Is there a way to use linqpad for xlinq? How to I add xml as a data source, or specify it in the query as a source?

[JoeAlbahari]

To query XML data in LINQPad, use XElement.Load or XDocument.Load. For example, set the query type to "C# Statements" and run the following:

CODE

var xml = XElement.Load (@"c:\\mydata.xml");

var query =
  from e in xml.Elements()
  where e.Attribute ("status").Value == "active"
  select e;

query.Dump();

You can also set the query type to "C# Expression" and do this in one step:

CODE

from e in XElement.Load ("filename-or-uri").Elements()
where e.Attribute ("status").Value == "active"
select e;

There's more info on querying XML in LINQPad's samples.

Cheers
Joe

[awilbourn]

To query XML data in LINQPad, use XElement.Load or XDocument.Load. For example, set the query type to "C# Statements" and run the following:

CODE

var xml = XElement.Load (@"c:\\mydata.xml");

var query =
  from e in xml.Elements()
  where e.Attribute ("status").Value == "active"
  select e;

query.Dump();

You can also set the query type to "C# Expression" and do this in one step:

CODE

from e in XElement.Load ("filename-or-uri").Elements()
where e.Attribute ("status").Value == "active"
select e;

There's more info on querying XML in LINQPad's samples.

Cheers
Joe

How would one query XML if there is a declared Namespace? Specifically I am trying to query an RDL file from reporting services and trying to pull out information for documentation purposes. I can run the code to dump the entire XML, but I cannot seem to get pinpointed to a particular area. Here is a subset of my RDL, with slight changes to hind some info:

CODE

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<InteractiveHeight>8.5in</InteractiveHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>11in</InteractiveWidth>
<rd:GridSpacing>0.0625in</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<DataSets>
<DataSet Name="usp_RPT_AccessorialList">
<Fields>
<Field Name="ACCESSORIAL_CD">
<DataField>ACCESSORIAL_CD</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DESCRIPTION_TXT">
<DataField>DESCRIPTION_TXT</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ADD_TO_INVOICE_FLG">
<DataField>ADD_TO_INVOICE_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PAY_TRACTOR_FLG">
<DataField>PAY_TRACTOR_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PAY_3RD_PARTY_FLG">
<DataField>PAY_3RD_PARTY_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PAY_PT_PCT_FLG">
<DataField>PAY_PT_PCT_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PERCENT_PAID_NUM">
<DataField>PERCENT_PAID_NUM</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="PayAgent">
<DataField>PayAgent</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PayCode">
<DataField>PayCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="BROKER_FLG">
<DataField>BROKER_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TERMINAL_CD">
<DataField>TERMINAL_CD</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EXPIRES_DTM">
<DataField>EXPIRES_DTM</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>myDB</DataSourceName>
<CommandType>StoredProcedure</CommandType>
<CommandText>usp_RPT_AccessorialList</CommandText>
<QueryParameters>
<QueryParameter Name="@KeyID">
<Value>=Parameters!KeyID.Value</Value>
</QueryParameter>
<QueryParameter Name="@TerminalCode">
<Value>=Parameters!TerminalCode.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
<DataSet Name="usp_RPT_GetTerminalListByLSUniqueID">
<Fields>
<Field Name="TerminalCode">
<DataField>TerminalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TerminalDescription">
<DataField>TerminalDescription</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>myDB</DataSourceName>
<CommandType>StoredProcedure</CommandType>
<CommandText>usp_RPT_GetTerminalListByLSUniqueID</CommandText>
<QueryParameters>
<QueryParameter Name="@KeyID">
<Value>=Parameters!KeyID.Value</Value>
</QueryParameter>
<QueryParameter Name="@IncludeNullForAllTerminals">
<Value>=Parameters!IncludeNullForAllTerminals.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
</DataSets>
<Height>0.5in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
</PageFooter>
<TopMargin>0.5in</TopMargin>
<PageHeight>8.5in</PageHeight>
</Report>

So I want to get information about the DataSets. I want to query each dataset and the query (CommandText) that makes up the dataset.

Thanks.

[awilbourn]

Sorry bad XML fragment, try this:

CODE

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<InteractiveHeight>8.5in</InteractiveHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>11in</InteractiveWidth>
<rd:GridSpacing>0.0625in</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<DataSets>
<DataSet Name="usp_RPT_AccessorialList">
<Fields>
<Field Name="ACCESSORIAL_CD">
<DataField>ACCESSORIAL_CD</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DESCRIPTION_TXT">
<DataField>DESCRIPTION_TXT</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ADD_TO_INVOICE_FLG">
<DataField>ADD_TO_INVOICE_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PAY_TRACTOR_FLG">
<DataField>PAY_TRACTOR_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PAY_3RD_PARTY_FLG">
<DataField>PAY_3RD_PARTY_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PAY_PT_PCT_FLG">
<DataField>PAY_PT_PCT_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PERCENT_PAID_NUM">
<DataField>PERCENT_PAID_NUM</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="PayAgent">
<DataField>PayAgent</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PayCode">
<DataField>PayCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="BROKER_FLG">
<DataField>BROKER_FLG</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TERMINAL_CD">
<DataField>TERMINAL_CD</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EXPIRES_DTM">
<DataField>EXPIRES_DTM</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>myDB</DataSourceName>
<CommandType>StoredProcedure</CommandType>
<CommandText>usp_RPT_AccessorialList</CommandText>
<QueryParameters>
<QueryParameter Name="@KeyID">
<Value>=Parameters!KeyID.Value</Value>
</QueryParameter>
<QueryParameter Name="@TerminalCode">
<Value>=Parameters!TerminalCode.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
<DataSet Name="usp_RPT_GetTerminalListByLSUniqueID">
<Fields>
<Field Name="TerminalCode">
<DataField>TerminalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TerminalDescription">
<DataField>TerminalDescription</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>myDB</DataSourceName>
<CommandType>StoredProcedure</CommandType>
<CommandText>usp_RPT_GetTerminalListByLSUniqueID</CommandText>
<QueryParameters>
<QueryParameter Name="@KeyID">
<Value>=Parameters!KeyID.Value</Value>
</QueryParameter>
<QueryParameter Name="@IncludeNullForAllTerminals">
<Value>=Parameters!IncludeNullForAllTerminals.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
</DataSets>
<TopMargin>0.5in</TopMargin>
<PageHeight>8.5in</PageHeight>
</Report>

[awilbourn]

One more post, I think I found how to query it now, add namespace for element I need so I can get just the datasets by doing the following:

CODE

var xml = XElement.Load (@"c:\\test.rdl");

var query =
from e in xml.Elements(@"{http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition}DataSets")
//where e.Attribute ("status").Value == "active"
select e;

query.Dump();

Hope this helps others. Great test tool LinqPad is!

[dharric]

Hello small problem. When I use

CODE

from e in XDocument.Load(@"C:\\cvs\\NETSBlotter_DFA\\PortfolioTrading\\NETSBlotter\\AlgoDataService\\AlgoDataService\\Users.xml").Descendants("Groups")
select e

it works fine. But when I filter with

CODE

where e.Attribute("name").Value.ToLower() == "admin" && e.Attribute("password").Value.ToLower() == "admin"

It says object reference not set to an instance of an object for e. I even tried using method syntax and I get the same error

CODE

XDocument xmlDoc = XDocument.Load(@"C:\cvs\NETSBlotter_DFA\PortfolioTrading\NETSBlotter\AlgoDataService\AlgoDataService\users.xml");
var user = xmlDoc.Descendants("Groups").Where(u => u.Attribute("name").Value.ToLower() == "admin" && u.Attribute("password").Value.ToLower() == "admin").FirstOrDefault();

[JoeAlbahari]

This means that there are some elements which don't have a name or password attribute, so the calling Attribute("name") or Attribute("password") returns null. Calling .ToLower() on a null throws a null reference exception. Instead, do this:

...
where ((string) e.Attribute ("name") ?? "").ToLower() == "admin" ...

Joe