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...
Last edited by nbrege; Jul 17th, 2023 at 03:58 PM.
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
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.
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.
...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.
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
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...
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.
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:
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?
Last edited by nbrege; Jul 21st, 2023 at 12:15 PM.
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.
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.
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...
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?
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...
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:
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:
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...
Last edited by nbrege; Jul 24th, 2023 at 01:05 PM.
'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
Reply With Quote
Originally Posted by wes4dbt
