Clicky

Hello EE,
I have a xml and in it I call a line where it looks like this :

<ss:Data ss:Type="String">2000.0000</ss:Data>

I need to change the value of ss:Type to  "Number"

How can i reach that with Xelement and XAttribute .. ?

I tried a little bit with XAttribute("ss:Type")   then SetAttribute but I am habing an error saying that name cannot have a ":"   in it

?? please help experts.

asked 12/12/2011 11:11

PhilippeRenaud's gravatar image

PhilippeRenaud ♦♦


9 Answers:
Can you provide an example of the full XML structure (including any namespaces)? Feel free to make up the data, or even obfuscate the element names.
link

answered

kaufmed's gravatar image

kaufmed

Yes here you go.
As a work example, search for the line:  <ss:Data ss:Type="String">$G_73$Y_TYA$P_11</ss:Data>

What I do in .net is I will repleace like I said With query the value $G_73$Y_TYA$P_11 with an amount.

So result in another xml is like : <ss:Data ss:Type="String">6000.0000</ss:Data>


but then if I open excel with that xml, I will see 6000.0000 but If I click on the cell, I will get  '6000.0000 with the ( ' )

Im sure its because I have the "String" Attribute.
link

answered 2011-12-13 at 07:58:07

PhilippeRenaud's gravatar image

PhilippeRenaud

Are you continuing with the code we worked on previously, or is this new/different code?
link

answered 2011-12-13 at 08:04:11

kaufmed's gravatar image

kaufmed

Same code with minor mods but dont worry about it
link

answered 2011-12-13 at 09:07:58

PhilippeRenaud's gravatar image

PhilippeRenaud

OK. The reason I ask is because if you haven't changed the Linq query, they you have access to the node, and it should be relatively easy to modify the attribute. The last code we had was here. Modifying that, you could access the attribute in this manner:

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
Imports System.Collections.Generic
Imports System.Linq
Imports System.Xml.Linq

Module Module1

    Sub Main()
        Dim doc As XDocument = XDocument.Load("test.xml")
        Dim ns As XNamespace = "urn:schemas-microsoft-com:office:spreadsheet"

        Dim result = From node In doc.Descendants() _
                     Where Not node.HasElements AndAlso node.Value.StartsWith("$") _
                     Select node

        For Each node In result
            Dim periods As System.Text.RegularExpressions.MatchCollection

            periods = System.Text.RegularExpressions.Regex.Matches(node.Value, "$G_(d+)$Y_(w+)$P_(d+)")

            If periods.Count > 0 Then
                ' Start with first match in list [periods(0)]
                Dim group As String = periods(0).Groups(1).Value ' First group of parentheses in pattern
                Dim year As String = periods(0).Groups(2).Value  ' Second group of parentheses in pattern
                Dim periodList As String = periods(0).Groups(3).Value ' Third group of parentheses in pattern

                ' Then iterate over the remaining matches, if necessary
                For i As Integer = 1 To periods.Count - 1
                    periodList += "," + periods(i).Groups(3).Value ' Third group of parentheses in pattern
                Next

                ' build query
                Dim query As String = "SELECT SUM(amount)" & _
                                      " FROM table" & _
                                      " WHERE year = " & year & _
                                      " AND period IN (" & periodList & ")" & _
                                      " AND Group = " & group

                ' Query SQL server
                Dim sum As Decimal ' = [query the DB]

                node.Value = sum.ToString()

            End If

            If node.Attribute(ns + "Type") IsNot Nothing AndAlso node.Attribute(ns + "Type").Value = "String" Then
                node.Attribute(ns + "Type").Value = "Number"
            End If

            System.Diagnostics.Debugger.Break()
        Next

        doc.Save("newfilename.xml") ' Save the changes. You can use the same name if you like; I changed it for your testing purposes

        System.Diagnostics.Debugger.Break()
    End Sub
End Module


Note the addition of lines 45 - 47, and the addition of the namespace in line 9. The namespace is needed because your nodes fall into a different namespace than the default (evidenced in part by the prefix on the node names).
link

answered 2011-12-13 at 09:08:40

kaufmed's gravatar image

kaufmed

alright, Thanks Kaufmed
link

answered 2011-12-13 at 10:16:14

PhilippeRenaud's gravatar image

PhilippeRenaud

Hey Kaufmed, a related question for you... can u help ?

http://www.qa.downappz.com/Programming/Languages/.NET/Visual_Basic.NET/Q_27550486.html



Thanks a lot...
link

answered 2011-12-13 at 10:22:43

PhilippeRenaud's gravatar image

PhilippeRenaud

link

answered 2012-01-24 at 06:35:32

PhilippeRenaud's gravatar image

PhilippeRenaud

I noticed. Thx for the heads up though   = )
link

answered 2012-03-05 at 07:58:16

kaufmed's gravatar image

kaufmed

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×59
×3
×19
×67

Asked: 12/12/2011 11:11

Seen: 235 times

Last updated: 12/13/2011 02:22