How to create linq query using namespace using ... syntax

Question

I'm confused as to how namespaces work. I'm trying to get the worksheet where ss:Name="Datagrid" and from that get the data node where name="emailname"

Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
Module Module1   
    Sub Main()
        Dim xmlFile As String = System.AppDomain.CurrentDomain.BaseDirectory & "Datagrid.xml"
        Dim root As XElement = XElement.Load(xmlFile)

    ''select worksheet where ss:Name="Datagrid""
    'Dim dg = From item In root .......................

    ''get data from wokrsheet...table..row...data where = name="emailname"  (not ss:name="emailname")

    'Dim data = From item In dg .......................
    End Sub
End Module

XML

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
  <Worksheet ss:Name="Datagrid">
    <Table ss:ExpandedColumnCount="13" ss:ExpandedRowCount="11" x:FullColumns="1"
     x:FullRows="1" ss:DefaultRowHeight="15">
      <Row ss:Index="3" ss:AutoFitHeight="0">
        <Cell Name="emailname">
          <Data ss:Type="String">email address</Data>
        </Cell>
      </Row>
      <Row ss:Index="4" ss:AutoFitHeight="0">
        <Cell Name="username">
          <Data ss:Type="String">user name</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
  <Worksheet ss:Name="Properties">
    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="3" x:FullColumns="1"
     x:FullRows="1" ss:DefaultRowHeight="15">
      <Row>
        <Cell>
          <Data ss:Type="Number">1</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">2</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">3</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
</Workbook>

Mark Hurd · Accepted Answer · 2012-08-20 10:33:57Z

1

Declare the namespaces with Imports statements at the top of the file. Then you can refer to them with the <ns:name> syntax.

So

Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">
Imports <xmlns:o="urn:schemas-microsoft-com:office:office">
Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">
Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
Imports <xmlns:html="http://www.w3.org/TR/REC-html40">

will give you the namespaces defined by the XML you've provided.

Now you could access <Workbook>...<Table>.@x:FullRows including in LINQ queries.

These XML features of VB.NET are referred to as Axis Properties in the Microsoft documentation.

I've pasted your XML into VS2008 and determined your issue is that the XML tags, including in Axis Properties, are case sensitive:

''select worksheet where ss:Name="Datagrid""
'Dim dg = From item In root .......................
Dim dg = root.<Worksheet>.FirstOrDefault(Function(w) w.@ss:Name = "Datagrid")

If dg Is Nothing Then _
    Throw New Exception("DataGrid not found")

''get data from wokrsheet...table..row...data where = name="emailname"  (not ss:name="emailname")

'Dim data = From item In dg .......................
' Both of the following lines work, but the second answers the question in the title:
'Dim data = dg.<Table>.<Row>.<Cell>.FirstOrDefault(Function(d) d.@Name = "emailname")
Dim data = dg...<Cell>.FirstOrDefault(Function(d) d.@Name = "emailname")

If data Is Nothing Then _
    Throw New Exception("emailname not found")

Console.WriteLine(data.<Data>.Value)

edited Aug 20, 2012 at 10:33

answered Aug 17, 2012 at 13:15

Mark Hurd

11k10 gold badges74 silver badges106 bronze badges

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

4 Comments

Mark Hurd Over a year ago

Hey, you're already using this syntax "item.@ss:Name"! So have I misunderstood your question?

WhiskerBiscuit Over a year ago

I think so, I'm trying to get the datagrid whose ss:Name="Datagrid" and from that get the <data> node whose name="data" (not ss:name="data")

Mark Hurd Over a year ago

Note the default namespace (xmlns) is the same as xmlns:ss, so name and ss:name is the same.

Mark Hurd Over a year ago

@WhiskerBiscuit In case you didn't notice my edit, I believe you were on the right track, except you needed to get the case of Name correct.

Channs · Accepted Answer · 2012-08-16 18:31:56Z

0

To get a cleaner syntax, suggest trying the Linq-to-XML XPath extension method shown below.

Update #1: The below code works if the XML file doesn't contain any namespaces. Working on a solution to consider namespaces.

Dim root = XElement.Load(xmlFile)
Dim data = root.XPathSelectElements("Worksheet[@Name=""Datagrid""]/Table/Row/Cell/Data[@Name=""edata""]")

edited Aug 16, 2012 at 18:31

answered Aug 16, 2012 at 18:02

Channs

2,1012 gold badges15 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to create linq query using namespace using ... syntax

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related