|
what is your exact question?
modified 20-Sep-20 21:01pm.
|
|
|
|
|
I have some data in Excel which represents structured lists to be imported into a SQL database in an XML format. I am not so familiar with the nuances of XML but I am pretty good with VBA. I need to generate an XML file that looks like this.
="1.0"
<!DOCTYPE xml>
-<xml>
-<transactions>
-<transaction vaultname="Type in the name of the vault here" date="123459" type="import_lists">
-<list name="Materials">
<item item_name="Ceramic"/>
<item item_name="Ferrous Metal"/>
<item item_name="Nonferrous Metal"/>
<item item_name="Superalloy"/>
</list>
-<list name="Ceramics">
<item item_name="Boride"/>
<item item_name="Carbide"/>
<item item_name="Nitride"/>
<link link_value="Ceramic" list_variable="Material Type" source_variable="My Material"/>
</list>
-<list name="Ferrous Metals">
<item item_name="Stainless Steel"/>
<item item_name="Carbon Steel"/>
<item item_name="Tool Steel"/>
<link link_value="Ferrous Metal" list_variable="Material Type" source_variable="My Material"/>
</list>
-<list name="Nonferrous Metals">
<item item_name="Aluminium Alloy"/>
<item item_name="Copper Alloy"/>
<item item_name="Tin Alloy"/>
<link link_value="Nonferrous Metal" list_variable="Material Type" source_variable="My Material"/>
</list>
-<list name="Superalloys">
<item item_name="Cobalt Base"/>
<item item_name="Iron Base"/>
<item item_name="Nickel base"/>
<link link_value="Superalloy" list_variable="Material Type" source_variable="My Material"/>
</list>
</transaction>
</transactions>
</xml>
I have the data in Excel and I would like to use VBA code similar to the below, but I am having a devil of a time getting it to output as shown above. When I change the tags to what I need in the sample output the result is just a mess. I am sure its my limited exposure to XML that has me stumbling. If someone could show me a sample set of the VBA that would output some of the above it would get me rolling again.
Dim FSO As Object
Dim NewFile As Object
Dim FullPath As String
Dim XMLFileText As String
FullPath = "C:\Users\Administrator\Desktop\Guy2.xml"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set NewFile = FSO.CreateTextFile(FullPath, 1, 1)
XMLFileText = ""
XMLFileText = XMLFileText & "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>" & vbNewLine
XMLFileText = XMLFileText & "<DATAROOT>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "<DATA>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "<c10>" & Range("C10") & "</c10>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "<D10>" & Range("D10") & "</D10>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "<E10>" & Range("E10") & "</E10>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "</DATA>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "<DATA>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "<C11>" & Range("C11") & "</C11>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "<D11>" & Range("D11") & "</D11>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "<E11>" & Range("E11") & "</E11>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "</DATA>" & vbNewLine
NewFile.Write (XMLFileText)
XMLFileText = "</DATAROOT>" & vbNewLine
NewFile.Write (XMLFileText)
NewFile.Close
modified 30-Sep-14 22:30pm.
|
|
|
|
|
You'll probably get better results if you use the MSXML components[^] to generate your XML document. Apart from anything else, it will take care of properly encoding your string values.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
XML
="1.0"="UTF-8"
<catalog>
<example>
:20:FT13261793408907
N23B:CRED
SA32A:130918USD111670,00
</example>
</catalog>
XSLT
="1.0"="UTF-8"
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each select="catalog">
<tr>
<td><xsl:value-of select="example"> </td>
</tr>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Current OUTPUT
:20:FT13261793408907 N23B:CRED SA32A:130918USD111670,00
Desired OUTPUT
:20:FT13261793408907
N23B:CRED
SA32A:130918USD111670,00
output must not be in a same line its must be as shown in the desired o/p
|
|
|
|
|
You don't mention if there can be one or more example nodes within each catalog node.
This code works if there are many catalog's with one example node.
="1.0"="utf-8"
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:output method="text" indent="no"/>
<xsl:template match="catalog">
<xsl:value-of select="example"/>
</xsl:template>
</xsl:stylesheet>
And this code works if there are many catalog nodes and many example nodes.
="1.0"="utf-8"
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:output method="text" indent="no"/>
<xsl:template match="catalog">
<xsl:apply-templates select="example" />
</xsl:template>
<xsl:template match="example">
<xsl:value-of select="node()"/>
</xsl:template>
</xsl:stylesheet>
XSLT is recursive by nature, so no need for the loop.
If you want text output, I don't understand the HTML tags in your example code.
|
|
|
|
|
Hi all, I have already raised my question in different area of this forum. Please have a look at my question from the below link for more information. Could you please help me to generate xml structure with mutiple namespces. TIA.
Multiple namespace in xslt[^]
|
|
|
|
|
Please do not do this; post your question in one place only.
|
|
|
|
|
So, should I post it here instead of quick answer section?
|
|
|
|
|
|
Hello,
I'd like to serialize an Objekt with XmlSerializer. The Objekt is a kind of treeview with lots of public properties like this:
public object Instance;
Currently I have different kind of classes stored in the Instance objects, all inheriting from the same base class. If I want to serialize these, I need to tell the XmlSerializer in an XmlAttributeOverrides object about the different Instance object classes. I collect this information dynamically before starting the XmlSerializer.
Setting a breakpoint just before that I can check the Overrides object which looks like this:
- Overrides {System.Xml.Serialization.XmlAttributeOverrides} System.Xml.Serialization.XmlAttributeOverrides
- Non-Public members
- types Count = 5 System.Collections.Hashtable
- [{CTrModules.MDataSaveXML}] {System.Collections.Hashtable}
+ Key {Name = "MDataSaveXML" FullName = "CTrModules.MDataSaveXML"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
+ Raw View
- [{CTrModules.QSM_Var_Dict}] {System.Collections.Hashtable}
+ Key {Name = "QSM_Var_Dict" FullName = "CTrModules.QSM_Var_Dict"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
+ Raw View
- [{System.String}] {System.Collections.Hashtable}
+ Key {Name = "String" FullName = "System.String"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
+ Raw View
- [{CTrModules.MHandleException}] {System.Collections.Hashtable}
+ Key {Name = "MHandleException" FullName = "CTrModules.MHandleException"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
+ Raw View
- [{FFV_Cockpit.InternalExcHandler}] {System.Collections.Hashtable}
+ Key {Name = "InternalExcHandler" FullName = "FFV_Cockpit.InternalExcHandler"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
Now here's my problem: I get a InvalidOperationException: "The type CTrModules.MDataSaveXML was not expected."
Looking through the Overrides above, CTrModules.MDataSaveXML was handed over to the serializer along with all other classes. All other classes can be serialized, but not this one. All classes have the Serializable attribute and are inheriting the same base class.
I'm at a loss here. Why is CTrModules.MDataSaveXML not expected?
I'm using C# in Visual Studio Espress 2013 for Windows Desktop, the target framework is .NET Framework 4.
TIA,
Christian
|
|
|
|
|
|
Hi Bernhard,
Bernhard Hiller wrote: Did you add the [XmlInclude(typeof(CTrModules.MDataSaveXML))]
attribute to the base class
I don't really want to go with that option, because then I have to remember to add such an include for any class I derive from the base class.
But I tried it just for test purposes: I get the same problem.
The option I chose to use is this one:
Bernhard Hiller wrote: or pass an array of types to the constructor of the serializer?
I wrote a method to gather all types in an array and I pass this array to the serializer.
I set a breakpoint at the call of the serializer and inspected this array:
- Overrides {System.Xml.Serialization.XmlAttributeOverrides} System.Xml.Serialization.XmlAttributeOverrides
- Non-Public members
- types Count = 5 System.Collections.Hashtable
- [{CTrModules.MDataSaveXML}] {System.Collections.Hashtable}
+ Key {Name = "MDataSaveXML" FullName = "CTrModules.MDataSaveXML"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
+ Raw View
- [{CTrModules.QSM_Var_Dict}] {System.Collections.Hashtable}
+ Key {Name = "QSM_Var_Dict" FullName = "CTrModules.QSM_Var_Dict"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
+ Raw View
- [{System.String}] {System.Collections.Hashtable}
+ Key {Name = "String" FullName = "System.String"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
+ Raw View
- [{CTrModules.MHandleException}] {System.Collections.Hashtable}
+ Key {Name = "MHandleException" FullName = "CTrModules.MHandleException"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
+ Raw View
- [{FFV_Cockpit.InternalExcHandler}] {System.Collections.Hashtable}
+ Key {Name = "InternalExcHandler" FullName = "FFV_Cockpit.InternalExcHandler"} object {System.RuntimeType}
- Value Count = 1 object {System.Collections.Hashtable}
+ ["Instance"] {System.Xml.Serialization.XmlAttributes}
It contains all classes, including CTrModules.MDataSaveXML. All other classes can be serialized, but not this one.
CU,
Christian
|
|
|
|
|
Hello, I have a xml file and try to select nodes with a xpath query based on multiple attribute values, but can't get it to work. please tell me the correct xpath.
here's part of the xml:
<AppInt ID="91" Type="Get_Rcp_Rel_Content">
<Tags>
<tag ID="300" ROLE="REQTRG" PLCID="PLC008" DB="113" ADDRESS="36" LENGTH="1" DATATYPE="INT" />
<tag ID="301" ROLE="RSPTRG" PLCID="PLC008" DB="113" ADDRESS="38" LENGTH="1" DATATYPE="INT" />
<tag ID="302" ROLE="modeID" PLCID="PLC008" DB="113" ADDRESS="40" LENGTH="1" DATATYPE="INT" />
<tag ID="303" ROLE="jobID" PLCID="PLC008" DB="113" ADDRESS="42" LENGTH="1" DATATYPE="INT" />
<tag ID="304" ROLE="RcpRelID" PLCID="PLC008" DB="113" ADDRESS="44" LENGTH="1" DATATYPE="INT" />
<tag ID="305" ROLE="Enable" PLCID="PLC008" DB="113" ADDRESS="46" LENGTH="1" BIT="0" DATATYPE="BOOL" />
<tag ID="306" ROLE="FillingTime" PLCID="PLC008" DB="113" ADDRESS="48" LENGTH="1" DATATYPE="LONG" />
<tag ID="307" ROLE="FillingRPM" PLCID="PLC008" DB="113" ADDRESS="52" LENGTH="1" DATATYPE="REAL" />
</Tags>
<Setpoints>
<Setpoint JobID="1002" ModeID ="1" Name="Enable" Value="True"/>
<Setpoint JobID="1002" ModeID ="1" Name="FillingTime" Value="5500"/>
<Setpoint JobID="1002" ModeID ="1" Name="FillingRPM" Value="53.2"/>
</Setpoints>
</AppInt>
and here's my xpath query:
//AppInt [@ID="91"]/Setpoints [Setpoint/@JobID="1002" and Setpoint/@ModeID="1"]/@Name
|
|
|
|
|
Which nodes are you trying to select?
|
|
|
|
|
You asked the right question: I was trying to select the setpoint nodes, so i must remove the @Name.
|
|
|
|
|
Try this:
//AppInt[@ID="91"]/Setpoints/Setpoint[@JobID="1002" and @ModeID="1"]/@Name
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello!
I have no possibility to use following-sibling axis.
How can I get the same functionality in XPath without using axes? Is it real?
Thanks in advance.
|
|
|
|
|
Guys, give me your thoughts on how you validate your xml at runtime. I recently fell into a drawn-out debug session where I couldn't see the problem with my xml file. I opened it in c#, read it, and checked for various attributes and what have you, adopting plan B that if I don't find an attribute I'll assume a default value.
The problem was I had an attribute called Recursion but I inadvertently spelt it Recursive, not the same thing! 
I know I could validate the xml with an xsd file which sounds great in principal but is that the preferred way to validate your xml or do you do it the "safer" (word used very loosely) way by keeping all the literal names in your code and then issue warnings if things don't pass muster?
The xsd file itself could for any number of reasons be subject to an incorrect edit. I'd like to know which way you do things to ensure that when it comes to the usage, your xml is as clean to use as you can make it. Ideas?
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
I would say an xsd file is the way to do it. You could possibly verify your xsd file with a CRC32 value.
|
|
|
|
|
First time I have to do this. I know, where have I been the last 10 years...
Given a structure:
struct {
int A;
int B;
another_struct aStruct;
float fValue;
} my_binary_data;
What is the best practice way to dump this in Xml format as well as read it? We're using rapidXml on this project, and what I've seen in existing code is line after line of sprint operations to format and then insert each tagged value into the Xml file. Pretty much the same thing trying to read the Xml back in.
My concern is that some of my structures are going to be changing day by day, and keeping the conversion code up to data is going to be tedious.
Anyone have a better way to do this, or is this just the nature of the beast?
Charlie Gilley
<italic>You're going to tell me what I want to know, or I'm going to beat you to death in your own house.
"Where liberty dwells, there is my country." B. Franklin, 1783
“They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety.” BF, 1759
|
|
|
|
|
Since XML is text based you have very little choice. Perhaps you need to investigate whether XML is the optimum solution to your problem.
Use the best guess
|
|
|
|
|
Appreciate the feedback. As in many projects, some things are being decided by other people. I'm just trying to get a handle on what to expect. If it has to be done this way, it has to be done this way  .
Past projects have exchanged data between devices using binary. This has disadvantages - byte swapping issues, messages tied to data structures, etc.
Charlie Gilley
<italic>You're going to tell me what I want to know, or I'm going to beat you to death in your own house.
"Where liberty dwells, there is my country." B. Franklin, 1783
“They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety.” BF, 1759
|
|
|
|
|
I am receiving xml from an external web service that I am consuming in a C# application.
The xsd has this line:
<xs:element type="xs:byte" name="contract_term"/>
Most of the files received have this element set to a value but I have just come across one that has it empty:
<contract_term/>
Is this valid, or should I raise it with the web service producer?
|
|
|
|
|
It's syntactically valid XML; what's the problem?
Use the best guess
|
|
|
|
|
I would expect that a value of 0 to 255 would be present for an element marked as byte and it should never be empty. It caused the .NET XmlSerializer to throw an exception when deserializing the data.
I have done a work around by deserializing to a string instead and having an additional property that returns the result parameter of byte.TryParse on the string, but it's a bit messy.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
