1 Attachment(s)
Need help building an XML document
I need to build an XML document from data read in from a csv file. I want to store the main body of the document in a string as follows:
Code:
Dim mainNode As String =
"<?xml version=""1.0"" encoding=""UTF-8""?>
<Job>
<ProductVersion>2022</ProductVersion>
<Unit>mm</Unit>
<Properties>
<Job>
<Information>
<Job>
<Name>Some Value</Name>
<Description></Description>
</Job>
</Information>
<Attributes>
<Parameter>
<Name>SI_UCS_Off</Name>
<Description>++UCS's Off</Description>
<Type>B</Type>
<!--Measurement|Meas|M|Degrees|Deg|D|Integer|Int|I|Boolean|Bool|B|Decimal|Dec|D|Text|T|Currency|Cur|C-->
<Value>1</Value>
<Style>Note</Style>
<!--Attribute|Note|Standard *standard is default if missing-->
</Parameter>
</Attributes>
</Job>
</Properties>
<Rooms>
' insert sub-nodes here
</Rooms>
</Job>"
I first need to fill in the <Name> node with a value. Then I want to define a sub-node as follows:
Code:
Dim roomNode As String =
" <Room>
<RoomProperties>
<Room>
<General>
<Name>Some Value</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies>
' insert sub-nodes here
</Assemblies>
</Order>
</Room>"
I need to set the <Name> node of this sub-node with a value, then insert the sub-node into the <Rooms> node of the main node.
Hopefully this makes sense. I'm not sure if I should use XmlDocument or XDocument. I'm hoping someone can help me get a start on this. I also need to add another sub-node under the <Assemblies> node, but if I can get help with the first part, I think I can figure it out. I haven't programmed in a few years so I'm a little rusty. Thanks...
***Edit: I attached a copy of what the full xml file needs to look like...
Re: Need help building an XML document
Don't use strings. Use XElement and XElement literals. You can even embed LINQ queries.
Code:
Dim job As XElement
Dim sv As String = "Some value"
job = <Job>
<ProductVersion>2022</ProductVersion>
<Unit>mm</Unit>
<Properties>
<Job>
<Information>
<Job>
<Name><%= sv %></Name>
<Description></Description>
</Job>
</Information>
<Attributes>
<Parameter>
<Name>SI_UCS_Off</Name>
<Description>++UCS's Off</Description>
<Type>B</Type>
<!--Measurement|Meas|M|Degrees|Deg|D|Integer|Int|I|Boolean|Bool|B|Decimal|Dec|D|Text|T|Currency|Cur|C-->
<Value>1</Value>
<Style>Note</Style>
<!--Attribute|Note|Standard *standard is default if missing-->
</Parameter>
</Attributes>
</Job>
</Properties>
<Rooms>
<!-- Room Nodes Here -->
</Rooms>
</Job>
Dim roomNode As XElement
Dim words() As String = {"one ", "two ", "three "}
For x As Integer = 1 To 3
roomNode = <Room>
<RoomProperties>
<Room>
<General>
<Name><%= From el In words Select el Take x %></Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies>
<%= <AssemblyNode></AssemblyNode> %>
</Assemblies>
</Order>
</Room>
job.<Rooms>.LastOrDefault.Add(roomNode)
Next
Re: Need help building an XML document
Thank you. I will give it a go...
Re: Need help building an XML document
Do you really need to build it one element at a time? Couldn't you import the csv file into a DataTable, then Add/edit/Delete as you like then create the XML file using yourDatatable.WriteXML method.
Re: Need help building an XML document
Quote:
Originally Posted by
wes4dbt
Do you really need to build it one element at a time? Couldn't you import the csv file into a DataTable, then Add/edit/Delete as you like then create the XML file using yourDatatable.WriteXML method.
one could, but then you're at the mercy of the library in determingin what the wraping tags are ... which the OP may not want given that they start with a template wrapper. That said, I would load the CSV into a datatable, manipulate it, then run through it to create the XML I want and insert that where it goes in the template.
-tg
Re: Need help building an XML document
Quote:
Originally Posted by
techgnome
...I would load the CSV into a datatable, manipulate it, then run through it to create the XML I want and insert that where it goes in the template.
-tg
I currently am loading the CSV into a datatable. It's inserting the data into the XML template that I need help with. I attached a sample file in my original post that shows how the XML file needs to be structured.
Re: Need help building an XML document
Quote:
Originally Posted by
nbrege
I currently am loading the CSV into a datatable. It's inserting the data into the XML template that I need help with. I attached a sample file in my original post that shows how the XML file needs to be structured.
Starting as before
Code:
Dim job As XElement
Dim sv As String = "Some value"
job = <Job>
<ProductVersion>2022</ProductVersion>
<Unit>mm</Unit>
<Properties>
<Job>
<Information>
<Job>
<!-- Simple LINQ -->
<Name><%= sv %></Name>
<Description></Description>
</Job>
</Information>
<Attributes>
<Parameter>
<Name>SI_UCS_Off</Name>
<Description>++UCS's Off</Description>
<Type>B</Type>
<!--Measurement|Meas|M|Degrees|Deg|D|Integer|Int|I|Boolean|Bool|B|Decimal|Dec|D|Text|T|Currency|Cur|C-->
<Value>1</Value>
<Style>Note</Style>
<!--Attribute|Note|Standard *standard is default if missing-->
</Parameter>
</Attributes>
</Job>
</Properties>
<Rooms>
<!-- Room Nodes Here, see below -->
</Rooms>
</Job>
Now table data used to create XML using LINQ
Code:
' made up table
Dim table As New DataTable
table.Columns.Add("Dosage", GetType(Integer))
table.Columns.Add("Drug", GetType(String))
table.Columns.Add("PatientID", GetType(String))
table.Rows.Add(25, "DRUG A", "11")
table.Rows.Add(53, "DRUG B", "22")
table.Rows.Add(47, "DRUG C", "33")
table.Rows.Add(69, "DRUG D", "44")
table.Rows.Add(98, "DRUG E", "55")
Dim roomNode As XElement
roomNode = <FOO>
<%= From rw In table.Rows.Cast(Of DataRow)()
Select <Room>
<RoomProperties PID=<%= rw("PatientID") %>>
<Room>
<General>
<Name><%= rw("Drug") %></Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies>
<AssemblyNode><%= rw("Dosage") %></AssemblyNode>
</Assemblies>
</Order>
</Room>
%></FOO>
job.<Rooms>.LastOrDefault.Add(roomNode.Elements) 'just the elements
Re: Need help building an XML document
Quote:
Originally Posted by
dbasnett
Starting as before
Code:
Dim job As XElement
Dim sv As String = "Some value"
job = <Job>
<ProductVersion>2022</ProductVersion>
<Unit>mm</Unit>
<Properties>
<Job>
<Information>
<Job>
<!-- Simple LINQ -->
<Name><%= sv %></Name>
<Description></Description>
</Job>
</Information>
Is there a way to insert a value into the <Name></Name> node without using <%= sv %>?
Re: Need help building an XML document
Quote:
Originally Posted by
nbrege
Is there a way to insert a value into the <Name></Name> node without using <%= sv %>?
Sure
Code:
job.<Properties>.<Job>.<Information>.<Job>.<Name>.Value = "Hello World"
Re: Need help building an XML document
Quote:
Originally Posted by
dbasnett
Sure
Code:
job.<Properties>.<Job>.<Information>.<Job>.<Name>.Value = "Hello World"
That is what I was looking for. Thank you. I've done this before years ago at a previous job, but I couldn't remember how I did it & couldn't find any example...
Re: Need help building an XML document
Quote:
Originally Posted by
nbrege
That is what I was looking for. Thank you. I've done this before years ago at a previous job, but I couldn't remember how I did it & couldn't find any example...
It is just the path and works because there was on instance. Multiple Job nodes would have resulted in only the first name changing. There are shortcuts that can be taken based on the uniqueness of the naming and the order. This
Code:
job...<Name>.Value = "Hello World"
would also work because the Name node you wanted to change was the first encountered.
I try to keep my naming unique FWIW. Good luck.
Re: Need help building an XML document
Thank you for the help so far. I am making progress. However, I have run into another issue. I am trying to add sub-elements to the main element inside a loop. First I insert a value into a node of the sub-element, then I insert the sub-element into the main element. The problem is that the value I inserted into the first sub-element changes value each time I insert another sub-element into the main element. Here is the code I'm currently using:
Code:
For Each row As DataRow In myDT.Rows 'loop thru the datatable
Dim roomName As String = row("Room").ToString
Dim myRoom As XElement = roomElement
myRoom.<RoomProperties>.<Room>.<General>.<Name>.Value = roomName
myJob.<Rooms>.FirstOrDefault.Add(myRoom)
Next
Here is the XML of the main element that gets created:
Code:
<Job>
<ProductVersion>2022</ProductVersion>
<Unit> mm</Unit>
<Properties>
<Job>
<Information>
<Job>
<Name>Test Job</Name>
<Description></Description>
</Job>
</Information>
<Attributes>
<Parameter>
<Name> SI_UCS_Off</Name>
<Description>++UCS's Off</Description>
<Type> B</Type>
<!--Measurement|Meas|M|Degrees|Deg|D|Integer|Int|I|Boolean|Bool|B|Decimal|Dec|D|Text|T|Currency|Cur|C-->
<Value>1</Value>
<Style> Note</Style>
<!--Attribute|Note|Standard *standard is default if missing-->
</Parameter>
</Attributes>
</Job>
</Properties>
<Rooms>
<Room>
<RoomProperties>
<Room>
<General>
<Name>Room E</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
</Room>
<Room>
<RoomProperties>
<Room>
<General>
<Name>Room B</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
</Room>
<Room>
<RoomProperties>
<Room>
<General>
<Name>Room C</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
</Room>
<Room>
<RoomProperties>
<Room>
<General>
<Name>Room D</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
</Room>
<Room>
<RoomProperties>
<Room>
<General>
<Name>Room E</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
</Room>
</Rooms>
</Job>
The <Name> node in the first sub-node (in red) has a value of "Room E", but it should be "Room A". Somehow it's getting changed. If I step thru the code "Room A" does get inserted into the <Name> node the first time thru, but in the succesive insertions, it gets changed. Can you see any issue with my code?
Re: Need help building an XML document
Code:
myRoom.<RoomProperties>.<Room>.<General>.<Name>.Value = roomName
myJob.<Rooms>.FirstOrDefault.Add(myRoom)
That selects a node and changes the value there... it's not doing what you think it is doing.
If you want to add a node you have to create NEW node, set it's values, then append that to the parent of where you want it.
Code:
Dim newRoom as XMLElement
set newRoom = new XMLElement
newRoom.<RoomProperties>.<Room>.<General>.<Name>.Value = roomName
myJob.<Rooms>.FirstOrDefault.Add(newRoom)
Something like that... not sure if that works to create hte inveneing tags or not... it's untested air code, so no guarnatees or wartranty on it.
On a related OCD note... you may have requirements on it, and if so, that's fine, feel free to ignore... but having a room tag in a room tag... seems.... odd.
Code:
<Room>
<RoomProperties>
<Room>
<General>
<Name>Room E</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
</Room>
Yes, that second Room tag is inside a RoomProperties tag, but the Room isn't a property of RoomPRoperties.... or it shouldn't...
Code:
<Room>
<RoomProperties>
<General>
<Name>Room E</Name>
</General>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
</Room>
feels cleaner and better... XML is already verbose enough without uneccessary tags. Again, if it's something you have control over, I'd consider changing it... if you're stuck with it because of reasons, feel free to ignore.
-tg
Re: Need help building an XML document
Quote:
Originally Posted by
techgnome
On a related OCD note... you may have requirements on it, and if so, that's fine, feel free to ignore... but having a room tag in a room tag... seems.... odd.
-tg
Thanks for the suggestion. I'll try that. As far as the room tag within a room tag, I have no control over that. That is dictated by a program called Cabinet Vision. It is their format. I just have to duplicate it...
Re: Need help building an XML document
To me it looks like you are doing this the hard way. are you able share a full sample of the CSV? It seems to me you could more easily create a dataset with nested relations then write the xml from the dataset using a variety of different write modes. Maybe I am being dense?
Re: Need help building an XML document
Quote:
Originally Posted by
nbrege
Thanks for the suggestion. I'll try that. As far as the room tag within a room tag, I have no control over that. That is dictated by a program called
Cabinet Vision. It is their format. I just have to duplicate it...
In post 7 I showed how to insert nodes.
Re: Need help building an XML document
Quote:
Originally Posted by
dbasnett
In
post 7 I showed how to insert nodes.
I followed your example & it does insert the node, but it's omitting the top level node, which I need:
Code:
<Room>
<RoomProperties>
<Room>
<General>
<Name></Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies>
</Assemblies>
</Order>
</Room>
This is what I'm getting:
Code:
<Job>
<ProductVersion>2022</ProductVersion>
<Unit> mm</Unit>
<Properties>
<Job>
<Information>
<Job>
<Name>Test Room</Name>
<Description></Description>
</Job>
</Information>
<Attributes>
<Parameter>
<Name> SI_UCS_Off</Name>
<Description>++UCS's Off</Description>
<Type> B</Type>
<!--Measurement|Meas|M|Degrees|Deg|D|Integer|Int|I|Boolean|Bool|B|Decimal|Dec|D|Text|T|Currency|Cur|C-->
<Value>1</Value>
<Style> Note</Style>
<!--Attribute|Note|Standard *standard is default if missing-->
</Parameter>
</Attributes>
</Job>
</Properties>
<Rooms>
<RoomProperties>
<Room>
<General>
<Name>Room A</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
<RoomProperties>
<Room>
<General>
<Name>Room B</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
<RoomProperties>
<Room>
<General>
<Name>Room C</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
<RoomProperties>
<Room>
<General>
<Name>Room D</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
<RoomProperties>
<Room>
<General>
<Name>Room E</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
</Rooms>
</Job>
Re: Need help building an XML document
Quote:
Originally Posted by
nbrege
I followed your example & it does insert the node, but it's omitting the top level node, which I need:
Code:
<Room>
<RoomProperties>
<Room>
<General>
<Name></Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies>
</Assemblies>
</Order>
</Room>
This is what I'm getting:
Code:
<Job>
<ProductVersion>2022</ProductVersion>
<Unit> mm</Unit>
<Properties>
<Job>
<Information>
<Job>
<Name>Test Room</Name>
<Description></Description>
</Job>
</Information>
<Attributes>
<Parameter>
<Name> SI_UCS_Off</Name>
<Description>++UCS's Off</Description>
<Type> B</Type>
<!--Measurement|Meas|M|Degrees|Deg|D|Integer|Int|I|Boolean|Bool|B|Decimal|Dec|D|Text|T|Currency|Cur|C-->
<Value>1</Value>
<Style> Note</Style>
<!--Attribute|Note|Standard *standard is default if missing-->
</Parameter>
</Attributes>
</Job>
</Properties>
<Rooms>
<RoomProperties>
<Room>
<General>
<Name>Room A</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
<RoomProperties>
<Room>
<General>
<Name>Room B</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
<RoomProperties>
<Room>
<General>
<Name>Room C</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
<RoomProperties>
<Room>
<General>
<Name>Room D</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
<RoomProperties>
<Room>
<General>
<Name>Room E</Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies></Assemblies>
</Order>
</Rooms>
</Job>
So the code I showed DOESN'T omit the node. Can't see the code you are using.
Re: Need help building an XML document
Quote:
Originally Posted by
dbasnett
So the code I showed DOESN'T omit the node. Can't see the code you are using.
This is the the code I am currently using:
Code:
Dim myJob As XElement = jobElement
myJob.<Properties>.<Job>.<Information>.<Job>.<Name>.Value = jobName
For Each row As DataRow In myDT.Rows
Dim roomName As String = row("Room").ToString.Trim
Dim myRoom As XElement = roomElement
myRoom.<RoomProperties>.<Room>.<General>.<Name>.Value = roomName
myJob.<Rooms>.LastOrDefault.Add(myRoom.Elements)
Next
And this is the definitions of jobElement & roomElement:
Code:
Public jobElement As XElement = <Job>
<ProductVersion>2022</ProductVersion>
<Unit> mm</Unit>
<Properties>
<Job>
<Information>
<Job>
<Name></Name>
<Description></Description>
</Job>
</Information>
<Attributes>
<Parameter>
<Name> SI_UCS_Off</Name>
<Description>++UCS's Off</Description>
<Type> B</Type>
<!--Measurement|Meas|M|Degrees|Deg|D|Integer|Int|I|Boolean|Bool|B|Decimal|Dec|D|Text|T|Currency|Cur|C-->
<Value>1</Value>
<Style> Note</Style>
<!--Attribute|Note|Standard *standard is default if missing-->
</Parameter>
</Attributes>
</Job>
</Properties>
<Rooms>
</Rooms>
</Job>
Code:
Public roomElement As XElement = <Room>
<RoomProperties>
<Room>
<General>
<Name></Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies>
</Assemblies>
</Order>
</Room>
Re: Need help building an XML document
Quote:
Originally Posted by
nbrege
This is the the code I am currently using:
Code:
Dim myJob As XElement = jobElement
myJob.<Properties>.<Job>.<Information>.<Job>.<Name>.Value = jobName
For Each row As DataRow In myDT.Rows
Dim roomName As String = row("Room").ToString.Trim
Dim myRoom As XElement = roomElement
myRoom.<RoomProperties>.<Room>.<General>.<Name>.Value = roomName
myJob.<Rooms>.LastOrDefault.Add(myRoom.Elements)
Next
And this is the definitions of jobElement & roomElement:
Code:
Public jobElement As XElement = <Job>
<ProductVersion>2022</ProductVersion>
<Unit> mm</Unit>
<Properties>
<Job>
<Information>
<Job>
<Name></Name>
<Description></Description>
</Job>
</Information>
<Attributes>
<Parameter>
<Name> SI_UCS_Off</Name>
<Description>++UCS's Off</Description>
<Type> B</Type>
<!--Measurement|Meas|M|Degrees|Deg|D|Integer|Int|I|Boolean|Bool|B|Decimal|Dec|D|Text|T|Currency|Cur|C-->
<Value>1</Value>
<Style> Note</Style>
<!--Attribute|Note|Standard *standard is default if missing-->
</Parameter>
</Attributes>
</Job>
</Properties>
<Rooms>
</Rooms>
</Job>
Code:
Public roomElement As XElement = <Room>
<RoomProperties>
<Room>
<General>
<Name></Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies>
</Assemblies>
</Order>
</Room>
That code doesn't remotely resemble the code I posted.
I can't see these variable definitions and how they are set,
jobElement
jobName
roomElement
Based on that if I had to guess what the issue is...
Change
Code:
myJob.<Rooms>.LastOrDefault.Add(myRoom.Elements)
To
Code:
myJob.<Rooms>.LastOrDefault.Add(myRoom)
Re: Need help building an XML document
Quote:
Originally Posted by
dbasnett
I can't see these variable definitions and how they are set,
jobElement
jobName
roomElement
Based on that if I had to guess what the issue is...
Change
Code:
myJob.<Rooms>.LastOrDefault.Add(myRoom.Elements)
To
Code:
myJob.<Rooms>.LastOrDefault.Add(myRoom)
The xElements are defined in a module (see my previous post for definitions).
I have actually found the solution. TG had the right idea in post #13. Have to use New.
Here is the code that works for me:
Code:
Dim myJob As XElement = jobElement
myJob.<Properties>.<Job>.<Information>.<Job>.<Name>.Value = jobName
For Each row As DataRow In myDT.Rows
Dim roomName As String = row("Room").ToString.Trim
Dim myRoom As XElement = New XElement(roomElement)
myRoom.<RoomProperties>.<Room>.<General>.<Name>.Value = roomName
myJob.<Rooms>.LastOrDefault.Add(myRoom)
Next
Thank you to the both of you for the help...
Re: Need help building an XML document
Same thing as this,
Code:
'the way this is written myJob and jobElement refer to the same thing
Dim myJob As XElement = jobElement ' New XElement(jobElement) ????
myJob.<Properties>.<Job>.<Information>.<Job>.<Name>.Value = jobName
Dim roomNodes As XElement
'note that the root of roomNodes is FOO
roomNodes = <FOO>
<%= From rw In myDT.Rows.Cast(Of DataRow)()
Let rm As String = DirectCast(rw("Room"), String).Trim
Select <Room><!-- roomElement -->
<RoomProperties>
<Room>
<General>
<Name><%= rm %></Name>
</General>
</Room>
</RoomProperties>
<Order>
<Assemblies>
</Assemblies>
</Order>
</Room>
%></FOO>
myJob.<Rooms>.LastOrDefault.Add(roomNodes.Elements) 'just the Room elements
'look at jobElement