How to loop through an XML file and extract a specific attribute from an element-CodePudding

I have an XML file that looks like the following;

;<?xml version="1.0" encoding="UTF-8"?>
<Transactions>
<bsp_ConsignmentTrans>
<Trans_ID>DltAdj220801-1</Trans_ID>
<SamplePartID>01600BU</AKWPartID>
<ESCPartID>01-003-001</DLPPartID>
<Quantity>2</Quantity>
<TransDate>2022-08-01T00:00:00.000</TransDate>
<TransType>Positive Adjustment</TransType>
<Source>DRT-CS</Source>
<Destination>DRT-CS</Destination>
<IsComplete>0</IsComplete>
<Reference>xmlfeed</Reference>
</bsp_ConsignmentTrans>
<bsp_ConsignmentTrans>
<Trans_ID>DltAdj220801-11</Trans_ID>
<SamplePartID>03-007-009</AKWPartID>
<ESCPartID>03-007-009</DLPPartID>
<Quantity>2</Quantity>
<TransDate>2022-08-01T00:00:00.000</TransDate>
<TransType>Positive Adjustment</TransType>
<Source>DRT-CS</Source>
<Destination>DRT-CS</Destination>
<IsComplete>0</IsComplete>
<Reference>xmlfeed</Reference>
</bsp_ConsignmentTrans>

My task is to read the TransDate and depending on what date it says, take all data from between the bsp_ConsignmentTrans elements, create a new file containing all of these from this file with that date.

For example, if the data passed in was 2022-08-01, I want to scan the entire file for <TransDate>2022-08-01T00:00:00.000</TransDate> and then create a new file with all of the data where the TransDate line is above.

I've used LINQ to XML however am a bit unfamiliar with it. My sample code currently reads the first entry in the XML file;

<bsp_ConsignmentTrans>
<Trans_ID>DltAdj220801-1</Trans_ID>
<SamplePartID>01600BU</AKWPartID>
<ESCPartID>01-003-001</DLPPartID>
<Quantity>2</Quantity>
<TransDate>2022-08-01T00:00:00.000</TransDate>
<TransType>Positive Adjustment</TransType>
<Source>DRT-CS</Source>
<Destination>DRT-CS</Destination>
<IsComplete>0</IsComplete>
<Reference>xmlfeed</Reference>
</bsp_ConsignmentTrans>

Rather than the entire file.

Source code below:

using System;
using System.Xml;
using System.Xml.Linq;
using System.IO;

namespace XML_Project
{
    class Program
    {
        static void Main(string[] args)
        {
            XDocument X = XDocument.Load(@"\\a-sql14-dev-01\Developers\TomTestXML\TransactionsDRTAdjusts_20220801_232142.xml");

            var transaction = X.Element("Transactions");
            var transdate = transaction.Element("bsp_ConsignmentTrans");
            var tempData = transdate.Element("TransDate");

            Console.WriteLine(transdate);
            Console.ReadLine();

        }

    }

}

CodePudding user response：

Here is a breakdown of steps you can use to get the data you want by using Linq to xml queries. This is assuming your xml is valid because what you have posted it not valid. I assume it is just a post typo.

//here you have the root element
var transaction = X.Element("Transactions");

//now you have all bsp_ConsignmentTrans inside the root
var transdates = transaction.Elements("bsp_ConsignmentTrans");

//Now you have all bsp_ConsignmentTrans that have the TransDate you are looking for
var matchingDates = transdates.Where(t => t.Element("TransDate").Value == "2022-08-01T00:00:00.000");

//Now loop over the matched elements and get the data
foreach (var element in matchingDates)
{
    var trans_id = element.Element("Trans_ID").Value;
    //do something with trans_id        

    //...etc...
}

CodePudding user response：

Transactions is the root element and it has bsp_ConsigmentTrans ELEMENTS (not a single ELEMENT). And BTW your XML is invalid, sligthly modified to correct.

void Main()
{
    //XDocument X = XDocument.Load(@"\\a-sql14-dev-01\Developers\TomTestXML\TransactionsDRTAdjusts_20220801_232142.xml");
    XDocument x = XDocument.Parse(myXML);

    var transactions = x.Element("Transactions").Elements("bsp_ConsignmentTrans")
    .Select(t => new
    {
        TransId = (string)t.Element("Trans_ID"),
        SamplePartId = (string)t.Element("SamplePartID"),
        ESCPartId = (string)t.Element("ESCPartID"),
        Quantity = (int)t.Element("Quantity"),
        TransDate = (DateTime?)t.Element("TransDate"),
        TransType = (string)t.Element("TransType"),
        Source = (string)t.Element("Source"),
        Destination = (string)t.Element("Destination"),
        IsComplete = (bool)t.Element("IsComplete"),
        Reference = (string)t.Element("Reference")
    });
    foreach (var t in transactions)
    {
        Console.WriteLine($"{t.TransId}, {t.TransDate}, {t.SamplePartId}, {t.ESCPartId} ...");
    }
}

static readonly string myXML = @"<?xml version=""1.0"" encoding=""UTF-8""?>
<Transactions>
    <bsp_ConsignmentTrans>
        <Trans_ID>DltAdj220801-1</Trans_ID>
        <SamplePartID>01600BU</SamplePartID>
        <ESCPartID>01-003-001</ESCPartID>
        <Quantity>2</Quantity>
        <TransDate>2022-08-01T00:00:00.000</TransDate>  
        
        <TransType>Positive Adjustment</TransType>
        <Source>DRT-CS</Source>
        <Destination>DRT-CS</Destination>
        <IsComplete>0</IsComplete>
        <Reference>xmlfeed</Reference>
    </bsp_ConsignmentTrans>
    <bsp_ConsignmentTrans>
        <Trans_ID>DltAdj220801-11</Trans_ID>
        <SamplePartID>03-007-009</SamplePartID>
        <ESCPartID>03-007-009</ESCPartID>
        <Quantity>2</Quantity>
        <TransDate>2022-08-01T00:00:00.000</TransDate>
        <TransType>Positive Adjustment</TransType>
        <Source>DRT-CS</Source>
        <Destination>DRT-CS</Destination>
        <IsComplete>0</IsComplete>
        <Reference>xmlfeed</Reference>
    </bsp_ConsignmentTrans>
</Transactions>";

CodePudding user response：

You can get all the relevant nodes using the following code

X.Root
 .Elements("bsp_ConsignmentTrans")
 .Where(t => DateTime.Parse(t.Element("TransDate").Value)) > someDate)

If you want, you can just remove the ones you don't want and save the rest

foreach(var node in X.Root
 .Elements("bsp_ConsignmentTrans")
 .Where(t => DateTime.Parse(t.Element("TransDate").Value)) < someDate))
{
    node.Remove();
}

X.Save(somepath);

CodePudding user response：

Use the following steps to convert the XML data to a C# object.

Create a test file, e.g. file.xml:

  <?xml version="1.0" encoding="UTF-8"?>
  <Transactions>
  <bsp_ConsignmentTrans>
  <Trans_ID>DltAdj220801-1</Trans_ID>
  <SamplePartID>01600BU</SamplePartID>
  <ESCPartID>01-003-001</ESCPartID>
  <Quantity>2</Quantity>
  <TransDate>2022-08-01T00:00:00.000</TransDate>
  <TransType>Positive Adjustment</TransType>
  <Source>DRT-CS</Source>
  <Destination>DRT-CS</Destination>
  <IsComplete>0</IsComplete>
  <Reference>xmlfeed</Reference>
  </bsp_ConsignmentTrans>
  <bsp_ConsignmentTrans>
  <Trans_ID>DltAdj220801-11</Trans_ID>
  <SamplePartID>03-007-009</SamplePartID>
  <ESCPartID>03-007-009</ESCPartID>
  <Quantity>2</Quantity>
  <TransDate>2022-08-01T00:00:00.000</TransDate>
  <TransType>Positive Adjustment</TransType>
  <Source>DRT-CS</Source>
  <Destination>DRT-CS</Destination>
  <IsComplete>0</IsComplete>
  <Reference>xmlfeed</Reference>
  </bsp_ConsignmentTrans>
  </Transactions>

Open a new project in Visual Studio and create a dummy class.
Copy the content of xml file and navigate to 'Edit' -> 'Paste Special' -> 'Paste XML as Classes'
Previous step converts the XML file to Classes.

To actually convert the content of the XML file to a C# object, use the next code lines:

  var serializer = new XmlSerializer(typeof(Transactions));
  StreamReader file = new StreamReader("file.xml"); // read file
  var transactions = serializer.Deserialize(file); // converts XML file content to the Transactions object.