Pipeline to fix the odd promotion of AS2To.
See StackOverflow – Why field is promoted as AS2ToInternal instead of AS2To

This C# pipeline code checks for that issues and fixes it by promoting the proper field. I never found out why it was happening and why it needed to be fixed.

<pre>
using System;
using System.Linq;
using System.Xml;
using System.Text;
using System.Collections;
using System.Runtime.InteropServices;
using System.ComponentModel;
using System.IO;
using System.Resources;
using System.Reflection;
using System.Drawing;
using Microsoft.BizTalk.Message.Interop;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Component.Utilities;
using Microsoft.XLANGs.RuntimeTypes;
using Microsoft.XLANGs.BaseTypes;

namespace Microsoft.Samples.BizTalk.Pipelines.CustomComponent
{
  /// <summary>
  /// Pipeline component which can't be placed into any receive or send
  /// pipeline stage and do a property promotion / distinguished fields 
  /// writing based on arbitrary XPath.
  /// </summary>
  [ComponentCategory(CategoryTypes.CATID_PipelineComponent)]
  [ComponentCategory(CategoryTypes.CATID_Any)]
    [System.Runtime.InteropServices.Guid("EC9794D0-AE8F-42B9-A7E7-02A876998158")]

    public class SetAS2ToPromotedField : 
    BaseCustomTypeDescriptor,
    IBaseComponent, 
    Microsoft.BizTalk.Component.Interop.IComponent, 
    IPersistPropertyBag,
    IComponentUI
  {

        private string _Encoding = "ASCII"; 

        public SetAS2ToPromotedField()
            : base(resourceManager)
    {
    }

    #region Design-time property(ies)

        
        #endregion

    #region IBaseComponent Members

    /// <summary>
    /// Gets a component description.
    /// </summary>
    [Browsable(false)]
    public string Description
    {
            get 
            {
                return "SetAS2To from AS2ToInternal"; 
            }
    }

    /// <summary>
    /// Gets a component name.
    /// </summary>
    [Browsable(false)]
    public string Name
    {
            get 
            {
        return "SetAS2To";
            }
    }

    /// <summary>
    /// Gets a component version.
    /// </summary>
    [Browsable(false)]
    public string Version
    {
      get
      {
        return "1.0";
      }
    }

    #endregion

    #region IComponentUI Members

    /// <summary>
    /// Validate component.
    /// </summary>
    /// <param name="projectSystem">Project system</param>
    /// <returns>Enumerator of error message collection</returns>
    public IEnumerator Validate(object projectSystem)
    {
      // Don't do any validation
      return null;
    }

    /// <summary>
    /// Gets a component icon.
    /// </summary>
    [Browsable(false)]
    public System.IntPtr Icon
    {
      get
      {
        //return ((Bitmap) resourceManager.GetObject("CompIcon")).GetHicon();
                return new System.IntPtr(); 
      }
    }

    #endregion

    #region IComponent Members

    /// <summary>
    /// Executes a pipeline component.
    /// </summary>
    /// <param name="pContext">Pipeline context</param>
    /// <param name="pInMsg">Input message</param>
    /// <returns>Outgoing message</returns>
    public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)
    {
      try
      {
        return ExecuteInternal(pContext, pInMsg);
      }
      catch(Exception e)
      {
        // Put component name as a source information in this exception,
        // so the event log in message could reflect this.
        e.Source = Name;
        throw e;
      }
    }

    #endregion

    #region IPersistPropertyBag Members

    /// <summary>
    /// Initialize component.
    /// </summary>
    public void InitNew()
    {
      // Do nothing
    }

    /// <summary>
    /// Get component class ID.
    /// </summary>
    /// <param name="classID"></param>
    public void GetClassID(out Guid classID)
    {
      classID = new Guid("1E7EC223-338E-4D4C-B275-FE9AD90ECE6C");
    }

    /// <summary>
    /// Load component properties from a property bag.
    /// </summary>
    /// <param name="propertyBag">Property bag</param>
    /// <param name="errorLog">Error log level</param>
    public void Load(IPropertyBag propertyBag, int errorLog)
    {
      if ( null == propertyBag )
        throw new ArgumentNullException("propertyBag");

            //string val = (string)ReadPropertyBag(pb, "XPath1");
            //if (val != null) _XPath1 = val;
            string val; 

            //val = (string)ReadPropertyBag(propertyBag, "FilenamePattern");
            //if (val != null) _FilenamePattern = val;
        }

    /// <summary>
    /// Save component properties to a property bag.
    /// </summary>
    /// <param name="propertyBag">Property bag</param>
    /// <param name="clearDirty">Clear dirty flag</param>
    /// <param name="saveAllProperties">Save all properties flag</param>
    public void Save(IPropertyBag propertyBag, bool clearDirty, bool saveAllProperties)
    {
      if ( null == propertyBag )
        throw new ArgumentNullException("propertyBag");

            //propertyBag.Write("FilenamePattern", _FilenamePattern);

    }

        #endregion

        #region Private Members

        /// <summary>
        /// Executes the logic for this component.
        /// </summary>
        /// <param name="pContext">Pipeline context</param>
        /// <param name="pInMsg">Input message</param>
        /// <returns>Outgoing message</returns>
        private IBaseMessage ExecuteInternal(IPipelineContext pContext, IBaseMessage pInMsg)
        {
            // Check arguments
            if (null == pContext)
                throw new ArgumentNullException("pContext");

            if (null == pInMsg)
                throw new ArgumentNullException("pInMsg");

            if (null == pInMsg.BodyPart)
                throw new ArgumentNullException("pInMsg.BodyPart");

            if (null == pInMsg.BodyPart.GetOriginalDataStream())
                throw new ArgumentNullException("pInMsg.BodyPart.GetOriginalDataStream()");

            //
            // Create a seekable read-only stream over the input message body part stream.
            //

            // Create a virtual stream, using GetOriginalDataStream() method on the IBaseMessagePart because
            // this call doesn't clone stream (instead of IBaseMessagePart.Data property).
            SeekableReadOnlyStream stream = new SeekableReadOnlyStream(pInMsg.BodyPart.GetOriginalDataStream());

            //
            // Create a new outgoing message, copy all required stuff.
            //

            // Create a new output message
            IBaseMessage outMessage = pContext.GetMessageFactory().CreateMessage();

            // Copy message context by reference
            outMessage.Context = pInMsg.Context;

            // Create new message body part
            IBaseMessagePart newBodyPart = pContext.GetMessageFactory().CreateMessagePart();

            // Copy body part properties by references.
            newBodyPart.PartProperties = pInMsg.BodyPart.PartProperties;

            // Neal added so we can change data in the message itself with replace commands
            StreamReader reader = new StreamReader(stream);
            string text = reader.ReadToEnd();

            byte[] byteArray;
            _Encoding = "ASCII";
            if (_Encoding == "UNICODE")
            {
                byteArray = System.Text.Encoding.Unicode.GetBytes(text);
            }
            else
            {
                byteArray = System.Text.Encoding.ASCII.GetBytes(text);
            }
            MemoryStream memStream = new MemoryStream(byteArray);

            string strAS2Namespace = "http://schemas.microsoft.com/BizTalk/2006/as2-properties";

            object objAS2To = pInMsg.Context.Read("AS2To", strAS2Namespace);
            string strAS2To = objAS2To != null ? objAS2To.ToString() : "";

            object objAS2From = pInMsg.Context.Read("AS2From", strAS2Namespace);
            string strAS2From = objAS2From != null ? objAS2From.ToString() : "";

            // Absolutely no idea why were are seeing the AS2To field promoted as AS2ToInternal. 
            // https://stackoverflow.com/questions/61464238/biztalk-as2-why-field-is-promoted-as-as2tointernal-instead-of-as2to 
            object objAS2ToInternal = pInMsg.Context.Read("AS2ToInternal", strAS2Namespace);
            string strAS2ToInternal = objAS2ToInternal != null ? objAS2ToInternal.ToString() : "";

            object objPreservedFileName = pInMsg.Context.Read("PreservedFileName", strAS2Namespace);
            string strPreservedFileName = objPreservedFileName != null ? objPreservedFileName.ToString() : "";

            string strHttpNamespace = "http://schemas.microsoft.com/BizTalk/2003/http-properties";
            object objHttpHeaders = pInMsg.Context.Read("InboundHttpHeaders", strHttpNamespace);
            string strHttpHeaders = objHttpHeaders != null ? objHttpHeaders.ToString() : "";

            // could not get debugger to work, so had to try Trace 
            string trcGUID = System.Guid.NewGuid().ToString();
            string trcData = "Filename=" + strPreservedFileName;
            string trcXML = "InboundHttpHeaders:" + strHttpHeaders;

            // If No AS2To found, then substitute the next best possible value by promoting it 
            string strNewAS2To = null;
            if (String.IsNullOrEmpty(strAS2To))
            {

                // NOTE: if these two don't work, we could also parse out values from the Httpheader 
                // (but not there, the value is AS2-To 
                if (!String.IsNullOrEmpty(strAS2ToInternal))
                {
                    strNewAS2To = strAS2ToInternal;
                }
                else
                {
                    throw new System.ApplicationException("Could not find any possible value for AS2To. " + 
                        " InboundHttpHeaders=" + strHttpHeaders 
                        );
                }

                // based on above logic, promote a new value to AS2To if needed 
                //pInMsg.Context.Write("AS2To", strAS2Namespace, strNewAS2To);
                pInMsg.Context.Promote("AS2To", strAS2Namespace, strNewAS2To);

            }

            // Set virtual stream as a data stream for the new message body part
            //newBodyPart.Data = stream;
            newBodyPart.Data = memStream;
            pContext.ResourceTracker.AddResource(memStream);

      // Copy message parts
      CopyMessageParts(pInMsg, outMessage, newBodyPart);

            //
            // Return outgoing message.
            //

            return outMessage;
    }

        public static long GetRandomNumberInRange(double minNumber, double maxNumber)
        {
            double dblRandomNumber = new Random().NextDouble() * (maxNumber - minNumber) + minNumber;
            long longRandomNumber = System.Convert.ToInt64(Math.Round(dblRandomNumber, 0));
            return longRandomNumber;
        }

    /// <summary>
    /// Copy message parts from source to destination message.
    /// </summary>
    /// <param name="sourceMessage">Source message</param>
    /// <param name="destinationMessage">Destination message</param>
    /// <param name="newBodyPart">New message body part</param>
    private void CopyMessageParts(IBaseMessage sourceMessage, IBaseMessage destinationMessage, IBaseMessagePart newBodyPart)
    {
      string bodyPartName = sourceMessage.BodyPartName;
      for (int c = 0; c < sourceMessage.PartCount; ++c)
      {
        string partName = null;
        IBaseMessagePart messagePart = sourceMessage.GetPartByIndex(c, out partName);
        if (partName != bodyPartName)
        {
          destinationMessage.AddPart(partName, messagePart, false);
        }
        else
        {
          destinationMessage.AddPart(bodyPartName, newBodyPart, true);
        }
      }
    }

    /// <summary>
    /// Gets a message type from the XML stream.
    /// </summary>
    /// <param name="stream">Seekable stream</param>
    /// <returns>Message type</returns>
    /// <exception cref="InvalidOperationException">If message type can't be determined</exception>
    private string GetMessageType(Stream stream)
    {
      // Fail if message stream is not seekable
      if (!stream.CanSeek)
        throw new InvalidOperationException("An attempt to get a message type for non-seekable stream");

      // Save the current stream position
      long position = stream.Position;
      try
      {
        // Create XmlTextReader over the message stream
        XmlTextReader reader = new XmlTextReader(stream);
        while (reader.Read())
        {
          // Check if current node in XmlTextReader is element and it's global
          if (XmlNodeType.Element == reader.NodeType && 0 == reader.Depth)
          {
            // Found a global element, now build message type as <namespaceURI>#<localName> 
            // if namespaceURI is not empty and <localName> if namespaceURI is empty.
            if (reader.NamespaceURI != null && reader.NamespaceURI.Length > 0)
            {
              return reader.NamespaceURI + '#' + reader.LocalName;
            }
            else
            {
              return reader.LocalName;
            }
          }
        }

        // Not found, fail
        throw new InvalidOperationException("Message type can't be determined");
      }
      finally
      {
        // Restore the stream position
        stream.Position = position;
      }
    }

    /// <summary>
    /// Read property bag in order to eat the argument exception which is thrown
    /// when properties are not populated.
    /// </summary>
    /// <param name="propertyBag">Property bag</param>
    /// <param name="propertyName">Property name</param>
    /// <returns>Property value</returns>
    private object ReadPropertyBag(IPropertyBag propertyBag, string propertyName)
    {
      object value;
      try
      {
        propertyBag.Read(propertyName, out value, 0);
        return value;
      }
      catch (ArgumentException)
      {
        // IPropertyBag.Read throws an ArgumentException if there are no properties in there.
        // Just return null in this case.
        return null;
      }
    }

    private SchemaList documentSchemaList = new SchemaList();

    private static PromotingMap promotingMap = new PromotingMap();
    private static ResourceManager resourceManager = new ResourceManager("Microsoft.Samples.BizTalk.Pipelines.CustomComponent.ArbitraryXPathPropertyHandler", Assembly.GetExecutingAssembly());
    private static PropertyBase messageTypeWrapper = new BTS.MessageType();

    //private const string DocumentSpecNamesPropertyName = "DocumentSpecNames";
    //private const string DocumentSpecNamespacesPropertyName = "DocumentSpecNamespaces";
    //private const char   DocumentSpecNamesSeparator = '|';
    
    #endregion
  }
}
</pre>

Did you ever want to know how to process (parse) or create EDI files by just using .NET C#? There is a great library that can help.

I’m working on a new Udemy class that is an Intro to EDI (Electronic Data Interchange). Other than using more expensive enterprise systems like BizTalk, I wanted to show students how they could both 1) parse EDI themselves, and 2) use any good .NET libraries that are available.

In doing some research on StackOverflow, I ran across EDI.NET. The author, Constantinos Leftheris, from Greece, was inspired and influenced by the NewtonSoft JSON library. You may know that the NewtonSoft library allows you to serialize and deserialize JSON from objects in memory, to and from disk files (or strings).

With EDI.Net, you can do the same with EDI. The only catch is, you have to create a C# class (he calls it a POCO class) that defines the interchange, including all the EDI fields you want to access. It uses C# attributes (or decorators) to tell the software the relationship between an EDI column and the class variable.

It handles X12, EDIFact and the older TRADACOMS.

For example, if you are familiar with the X12 850 Purchase Order,
it a a “BEGIN” segment like this:


BEG*00*SA*1000012**20090827~

You interpret it by looking at an implementation guide, such as the one below:

In the example above, the “*” is the delimiter or separator character from each element. You then count over and each element has a sequential name, BEG01, BEG02, BEG03 (etc) based on the Segment Name “BEG” and a two digit number.

This tells us that BEG03 is the Purchase Order Number, and BEG05 is the PurchaseOrderDate. BEG04 is not used on a new PO, only for revisions to existing PO (which many companies do not support). Note that it has the letter “O” for Optional in the required column, while the other fields have “M” for Mandatory.

You can also learn from the above implementation guide that the PO Number (BEG03) can be from 1 to 22 alphanumeric (AN) characters.

The “type” for BEG05 is “DT” (Date) and it’s min/max length are both 8. The comment tells us that is must in the format CCYYMMDD.

Note in the attributes below, that “BEG/2” is zero-based, representing BEG03. It uses COBOL-like “Picture” clauses to describe the length and type of number in the field.

The sample above has BEG01=SA, which is not in the implementation guide I included above. Each company will hvae its own implementation guide and they may vary slightly as to code values and other nuances. But certain things like the PO Number being in BEG03 and the PODate being in BEG05 should never change.

<pre>
            [EdiValue(Path = "BEG/2", Description = "BEG03 - Purchase Order Number")]
            public string PurchaseOrderNumber { get; set; }

            [EdiValue("9(8)", Path = "BEG/4", Format = "yyyyMMdd", Description = "BEG05 - Purchase Order Date")]
            public string PurchaseOrderDate { get; set; }

</pre>

Deserializing is the process of taking an EDI file and loading into an object made from the POCO class. To create a new EDI file, you would be the object in the C# program, then serialize it to disk as an EDI file.

Download the zip

To do the demo below, you will need either download the .zip file from the GitHub or do a Git Clone. You will be needing the the “POCO” class called “PurchaseOrder850” in X12_850.xs.

Instructions for an easy X12 demo

  1. Create new C# console program, put code below in the program.cs.
  2. From top menu, click “Tools”, “NuGet Package Manager”, “Package Manager Console”, then type in “Install-Package “indice.Edi”.
  3. In Solution Explorer, click “add” then “existing item”, and select indice.Edi.Tests\Models\X12_850.cs (from the GitHub zip download).
  4. Adjust the filename to match where you put the files on your disk.
  5. Run it with the sample
  6. Then try it one of your EDI 850 files if you have one.
  7. Customize the C# POCO class as needed.

<pre>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using indice.Edi;
using indice.Edi.Tests.Models;
using System.IO; 

namespace EDINetDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string inputEDIFilename = @"c:\EDI.Net-master\test\indice.Edi.Tests\Samples\x12.850.edi";
            //string inputEDIFilename = @"c:\EDIClass\Samples\Sample_850_01_Orig.edi";

            var grammar = EdiGrammar.NewX12();
            grammar.SetAdvice(
                segmentNameDelimiter: '*',
                dataElementSeparator: '*',
                componentDataElementSeparator: '>',
                segmentTerminator: '~',
                releaseCharacter: null,
                reserved: null,
                decimalMark: '.');

            
            var po850 = default(PurchaseOrder_850);
            using (var stream = new StreamReader(inputEDIFilename))
            {
                po850 = new EdiSerializer().Deserialize<PurchaseOrder_850>(stream, grammar);

                // If you have only one ST and one PO/850 per file, 
                // you can use subscript 0, 
                // otherwise you will need loops here. 
                Console.WriteLine("PO Number:" + 
                  po850.Groups[0].Orders[0].PurchaseOrderNumber);
                Console.WriteLine("PO Date:" + 
                  po850.Groups[0].Orders[0].PurchaseOrderDate);

                foreach (var lineitem in po850.Groups[0].Orders[0].Items)
                {
                    Console.WriteLine(" LineItem:");
                    Console.WriteLine("  ItemNum=" + lineitem.OrderLineNumber);
                    Console.WriteLine("  Qty=" + lineitem.QuantityOrdered);
                    Console.WriteLine("  Price=" + lineitem.UnitPrice);
                    Console.WriteLine("  PartNo=" + lineitem.BuyersPartno);
                    Console.WriteLine("  Descr=" + lineitem.ProductDescription);
                }
                // 1) store PO into Database
                //    (create SQL statements or call Stored Proc) 
                // 2) write to XML for ERP system 
                // 3) call some web service 

            }

            Console.WriteLine("\n\n Press enter to end:");
            Console.ReadLine();
        }
    }
}
</pre>

This is an enhancement to a prior blog: PowerShell RegEx Parse EDI. It takes that example to the next step and uses the parsed data to rename the file. It also formats the file by changing the end segment character to the same followed by a carriage-return and line feed.

NOTE: Use this program at your own risk. I suggest making a copy of the files you want to test with, then work on the copy, and improve or tweak this program as needed to customize to your specific needs.

Things you probably need to change:

  1. Obviously the $DirName, and then whether you want to run it recursive or not.
    Or you might want to give an array of directory names, depending how you store and name your files.

  2. It’s currently assuming the files are EDI 850 Purchase Orders, as it is parsing specifically for the BEG segment (BEG03=PONum, BEG0=PO-Date). If you want to handle a wider variety of files, then don’t include these in the file rename. Or add additional logic to parse the files you want to parse and rename.
  3. If you have segment separators other than tilda, you will need about 2-3 lines of code to parse the ISA segment, get the actual separator
  4. Filter – Maybe *.*, *.txt or whatever to match your EDI file names.

You can set this to run as a scheduled task in Windows Task Scheduler. It was written so that you can run the same program over and over on the same directory. Files that get renamed get a double-underscore put in the name. So files that already have a double-underscore can be skipped.

<pre>
cls
$ErrorActionPreference = "Stop"
$DirName = "c:\EDIClass\ArchiveTest"

$files = Get-ChildItem $Dirname -Recurse -Filter *.edi  
foreach ($file in $files) 
{

    $fullname = $file.FullName.ToString();  
    $dirname = $file.Directory.ToString(); 
    $filename = $file.Name.ToString(); 

    $posTwoUnderScores = $filename.IndexOf("__") 
    if ($posTwoUnderScores -lt 0) 
    {

        Write-Host "OldName $fullname"
        $content = Get-Content $file.FullName

        Write-Host "RowCount=$($content.Count)"

        if ($content.Count -le 1) ## more than one line in file 
        {
           # break the file into separate lines, 
           # but save the date/times and restore them 
           # after we update the file 
           $origCreationTime  =  $file.CreationTime
           $origLastWriteTime =  $file.LastWriteTime
           Write-Host "Creation    Time: origCreationTime"
           Write-Host "Last Write  Time: origLastWriteTime"

           $content = $content.Replace("~","~`r`n")
           set-content $fullname $content 
           (Get-ChildItem $file.FullName).CreationTime = $origCreationTime
           (Get-ChildItem $file.FullName).LastWriteTime = $origLastWriteTime
        }

        $CompanyID  = [regex]::match($content,'.*ISA\*.*?\*.*?\*.*?\*.*?\*.*?\*(.*?)\*.*').Groups[1].Value
        $CompanyID  = $CompanyID.Trim()
        $ControlNum = [regex]::match($content,'.*ISA\*.*?\*.*?\*.*?\*.*?\*.*?\*.*?\*.*?\*.*?\*.*?\*.*?\*.*?\*.*?\*(.*?)\*.*').Groups[1].Value
        $OrderNum   = [regex]::match($content,'.*BEG\*.*?\*.*?\*(.*?)\*.*').Groups[1].Value
        $OrderDate  = [regex]::match($content,'.*BEG\*.*?\*.*?\*.*?\*.*?\*(.*?)[~\*].*').Groups[1].Value
        $EdiDocType = [regex]::match($content,'.~ST\*(.*?)[~\*].*').Groups[1].Value
        Write-Host "$OrderNum $OrderDate EdiDocType $CompanyID $ControlNum"

        Write-Host "Filename=$filename"
        $newFileName = $dirname + "\" + $CompanyID + "_" + $EdiDocType + "__" + $OrderNum + "_" + $OrderDate  + "_" + $filename
        Write-Host "NewName $newFileName`n" 
        Rename-Item $fullname $newFileName 
    } #end test for if file contains two underscores already 
} #end for-each loop
</pre>

The following relates to the previous two blogs. You can get the surrounding code in that C# LINQ/XML example blog.

LINQ can run against different data sources, including XML an SQL. So once you learn how to write the query expressions as shown below, you can switch back and forth between them with ease.

This is a sample from Complete Guide to XML for Microsoft Developers (Udemy Course).


            // GOT = Game of Thrones (data) 
            XElement xelGOT =
              new XElement("GameOfThrones",
                 new XElement("Characters",
                     new XElement("Character",
                         new XAttribute("Status", "deceased"),
                         new XElement("Name", "Daenerys Targaryen"),
                         new XElement("Actor", "Emilia Clarke")
                         ),
                     new XElement("Character",
                         new XAttribute("Status", "living"),
                         new XElement("Name", "Jon Snow"),
                         new XElement("Actor", "Kit Harrington")
                         ),
                     new XElement("Character",
                         new XAttribute("Status", "living"),
                         new XElement("Name", "Tyrion Lannister"),
                         new XElement("Actor", "Peter Dinklage")
                         )
                      ),
                 new XElement("Cities",
                     new XElement("City",
                         new XElement("Name", "King's Landing"),
                         new XElement("Location", "The Crownlands, Westeros")
                         ),
                     new XElement("City",
                         new XElement("Name", "Braavos"),
                         new XElement("Location", "The Free Cities, Essos")
                         ),
                     new XElement("City",
                         new XElement("Name", "Lannisport"),
                         new XElement("Location", "The Westerlands, The Seven Kingdoms")
                         )
                      )
                   );

 


        
        public static void queryExpressionsInLinq(XElement xelGOT)
        {
            Console.WriteLine(separator); 
            // Get second living character (sorted by character name) 
            // Error: at least one object must implement IComparable 
            string secondLivingCharacter =
                (from xchar in xelGOT.Elements("Characters").First().Elements("Character")
                 where (string)xchar.Attribute("Status").Value == "living"
                 orderby xchar.Element("Name").Value ascending  
                 select xchar).ElementAt(1).Element("Name").Value;
            Console.WriteLine("secondLivingCharacter=" + secondLivingCharacter); 

            // Get all the living characters 
            // Note the better way to test for a value equals something... 
            // the .Equals() method does not require casting 
            var results =   
                (from xchar in xelGOT.Elements("Characters").First().Elements("Character")
                where xchar.Attribute("Status").Value.Equals("living")
                select xchar);

            // Loop and show multiple matches 
            Console.WriteLine("\n\n============ Start: Query Living Characters ======");
            foreach (var result in results)
            {
                Console.WriteLine(
                          " Character=" + result.Element("Name").Value +
                          " Actor=" + result.Element("Actor").Value +
                          " Status=" + result.Attribute("Status").Value

                     );  ;
            }
            Console.WriteLine("============ End: Loop through Living Characters ======\n\n");

        }


 

The following relates to this XML from yesterday’s blog. You can get the surrounding code in that blog.
It’s advisable to use the LINQ style of query when possible, but there might be that day where you have a long complicated XPath, and you just want to use with an XElement that you loaded from LINQ. This is how to do that, with several examples.

This is a sample from Complete Guide to XML for Microsoft Developers (Udemy Course).


            // GOT = Game of Thrones (data) 
            XElement xelGOT =
              new XElement("GameOfThrones",
                 new XElement("Characters",
                     new XElement("Character",
                         new XAttribute("Status", "deceased"),
                         new XElement("Name", "Daenerys Targaryen"),
                         new XElement("Actor", "Emilia Clarke")
                         ),
                     new XElement("Character",
                         new XAttribute("Status", "living"),
                         new XElement("Name", "Jon Snow"),
                         new XElement("Actor", "Kit Harrington")
                         ),
                     new XElement("Character",
                         new XAttribute("Status", "living"),
                         new XElement("Name", "Tyrion Lannister"),
                         new XElement("Actor", "Peter Dinklage")
                         )
                      ),
                 new XElement("Cities",
                     new XElement("City",
                         new XElement("Name", "King's Landing"),
                         new XElement("Location", "The Crownlands, Westeros")
                         ),
                     new XElement("City",
                         new XElement("Name", "Braavos"),
                         new XElement("Location", "The Free Cities, Essos")
                         ),
                     new XElement("City",
                         new XElement("Name", "Lannisport"),
                         new XElement("Location", "The Westerlands, The Seven Kingdoms")
                         )
                      )
                   );

 


        public static void XPathLinqQueries(XElement xelGOT)
        {
            //
            // NOTE: The above is the preferred methodology using LINQ. 
            // In theory, you would use LINQ queries instead of XPATH 
            // queries, but there are two XPATH methods we will demonstrate. 
            //

            //Second Actor using XPath 
            string xpathCharacter2A = "./Characters/Character[2]/Actor";
            var secondActorViaXPathA = xelGOT.XPathSelectElement(xpathCharacter2A).Value;
            Console.WriteLine("Second Actor via XPathA=" + secondActorViaXPathA);

            // NOTE: XPathSelectElement only returns XElements, 
            // so you cannot do count(//Characters) or /Actor/text(). 
            // Instead, you have to use the more general XPathEvaluate.
            // But you must also wrap the xpath with like this: 
            // string(varXPath) 
            // The XPathEvaluate method returns an object that can contain
            // a bool, a double, a string, or an or IEnumerable.

            string xpathCharacter2B = "./Characters/Character[2]/Actor/text()";
            string secondActorViaXpathEval =
                  xelGOT.XPathEvaluate("string(" + xpathCharacter2B + ")") as string;
            Console.WriteLine("Second Actor via XPathB=" + secondActorViaXpathEval);

            string xpathCharacterCount = "count(./Characters/Character)";
            string strCharacterCount =
                  xelGOT.XPathEvaluate("string(" + xpathCharacterCount + ")") as string;
            Console.WriteLine("CharacterCount=" + strCharacterCount);

            double? nullableCharacterCount = xelGOT.XPathEvaluate(xpathCharacterCount) as double?;
            Console.WriteLine("Numeric CharacterCount=" + nullableCharacterCount);

            string xpathCharacter2C = "./Characters/Character";
            IEnumerable results = (IEnumerable)xelGOT.XPathEvaluate(xpathCharacter2C);
            //Loop through Characters 
            Console.WriteLine("\n\n============ Start: XPath Loop through Characters ======");
            foreach (var result in results.Cast())
            {
                Console.WriteLine(
                          " Character=" + result.Element("Name").Value +
                          " Actor=" + result.Element("Actor").Value +
                          " Status=" + result.Attribute("Status")

                     );
            }
            Console.WriteLine("============ End: Loop through Characters ======\n\n");

        }

 

Example of how to create XML in memory using LINQ, then how to retrieve elements and attributes from it in the LINQ style.
In a future blog, I will show you how to use XPath, and then LINK Query Expressions.

This is a sample from Complete Guide to XML for Microsoft Developers (Udemy Course).

using System;
//using System.Collections;
using System.Collections; //cannot use this one, as it causes clash with IEnumerable
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Linq; // for XElement
using System.Xml.XPath; // in order to use XPathSelectElement(s)

namespace LINQ_Practice
{
class Program
{
public static string separator = “\n\n——————————————–\n”;
static void Main(string[] args)
{

//LINQ – Language Integrated Query

/*
string xmlInFilename = @”c:\XMLClass\IntroSamples\Flight03.xml”;
// NOTE: we don’t have to instantiate XElement
XElement xelFlight3 = XElement.Load(xmlInFilename);
Console.WriteLine(xelFlight3.ToString());

// Per MSDN article:
// Only use XDocument when you need to add a comment
// or processing instruction at the root level.
XDocument xdocFlight3 = XDocument.Load(xmlInFilename);
Console.WriteLine(separator);
Console.WriteLine(xdocFlight3.ToString());

XElement xelCourse = XElement.Parse(“Complete Guide to XML for Microsoft DevelopersNeal Walters“);
Console.WriteLine(separator);
Console.WriteLine(xelCourse.ToString());

*/

// GOT = Game of Thrones (data)
XElement xelGOT =
new XElement(“GameOfThrones”,
new XElement(“Characters”,
new XElement(“Character”,
new XAttribute(“Status”, “deceased”),
new XElement(“Name”, “Daenerys Targaryen”),
new XElement(“Actor”, “Emilia Clarke”)
),
new XElement(“Character”,
new XAttribute(“Status”, “living”),
new XElement(“Name”, “Jon Snow”),
new XElement(“Actor”, “Kit Harrington”)
),
new XElement(“Character”,
new XAttribute(“Status”, “living”),
new XElement(“Name”, “Tyrion Lannister”),
new XElement(“Actor”, “Peter Dinklage”)
)
),
new XElement(“Cities”,
new XElement(“City”,
new XElement(“Name”, “King’s Landing”),
new XElement(“Location”, “The Crownlands, Westeros”)
),
new XElement(“City”,
new XElement(“Name”, “Braavos”),
new XElement(“Location”, “The Free Cities, Essos”)
),
new XElement(“City”,
new XElement(“Name”, “Lannisport”),
new XElement(“Location”, “The Westerlands, The Seven Kingdoms”)
)
)
);

Console.WriteLine(separator);
Console.WriteLine(xelGOT.ToString());

string xmlOutputFilename = @”c:\XMLClass\IntroSamples\GameOfThrones.xml”;
//xelGOT.Save(xmlOutputFilename);

standardLinqQueries(xelGOT);
//XPathLinqQueries(xelGOT);
//queryExpressionsInLinq(xelGOT);

Console.WriteLine(separator);

Console.WriteLine(“\n\n Press enter to end ….”);
Console.ReadLine();
}

public static void standardLinqQueries(XElement xelGOT)
{
// Our XElement is “GameOfThrones” so the
// navigation and XPATH below must start from there

Console.WriteLine(separator);
// NOTE: Don’t put root element in quotes…
string firstActor = xelGOT.Elements(“Characters”).First().Elements(“Character”).First().Elements(“Actor”).First().Value;
Console.WriteLine(“First Actor=” + firstActor);

//illustrate ElementAt method – zero-based
string secondActor = xelGOT.Elements(“Characters”).Elements(“Character”).ElementAt(1).Elements(“Actor”).First().Value;
Console.WriteLine(“Second Actor=” + secondActor);

//illustrate Skip method
string thirdActor = xelGOT.Elements(“Characters”).Elements(“Character”).Skip(2).Elements(“Actor”).First().Value;
Console.WriteLine(“Third Actor=” + thirdActor);

//Loop through Characters
Console.WriteLine(“\n\n============ Start: Loop through Characters ======”);
foreach (XElement xelChar in xelGOT.Elements(“Characters”).Elements(“Character”))
{
Console.WriteLine(
” Character=” + xelChar.Element(“Name”).Value +
” Actor=” + xelChar.Element(“Actor”).Value +
” Status=” + xelChar.Attribute(“Status”).Value
);
}
Console.WriteLine(“============ End: Loop through Characters ======\n\n”);

}

}
}

JavaScript programmers love JSON because it is “baked-in” to the JavaSCript programmnig language.

For those of using C#, Python, or Java, we have to use class libraries to assist us with the normal functions that are super-easy in JavaScript.

This blog shows you most of you what you need to know about JSON in one sample program. Ok, granted, I don’t show you how to do a request/response to a REST web service, but that’s only way one JSON is used.

<pre>
console.log("-------------------- Start JSON Demo:" + (new Date()).toLocaleString()); 

// Show how it easy it is to create a real object in 
// Javascript on the fly, with values, in one line of code! 
var flightObject = { flightNumber: "2501", passenger: "Neal Walters", DepartureAirport: "DFW", ArrivalAirport: "LAX"};

// Immediately you are able to access the properties 
// of that object. 
console.log("FlightNumber=" + flightObject.flightNumber); 
console.log("Passenger=" + flightObject.passenger); 

// Dynamically add a new properties to the object 
flightObject.Airline = "Southwest Airlines";
flightObject.AirlineCode = "WN";

// Now deserialize the object to a JSON string 
// (couldn't they have called the method JSON.deserialize?)
var flightJSONString = JSON.stringify(flightObject); 
console.log("flightJSONString=" + flightJSONString);

// Now create a new object from a JSON string 
// (couldn't they have called the method JSON.serialize?)
var flightObj2 = JSON.parse(flightJSONString)
console.log("flightObj2.AirlineName=" + flightObj2.Airline);

console.log("-------------------- End JSON Demo:" + (new Date()).toLocaleString()); 
</pre></con>

<h4>Sample output</h4>
<code><pre>
-------------------- Start JSON Demo:6/12/2020, 10:22:11 AM
flightObject=[object Object]
FlightNumber=2501
Passenger=Neal Walters
flightJSONString={"flightNumber":"2501","passenger":"Neal Walters","DepartureAirport":"DFW","ArrivalAirport":"LAX","Airline":"Southwest Airlines","AirlineCode":"WN"}
flightObj2.AirlineName=Southwest Airlines
-------------------- End JSON Demo:6/12/2020, 10:22:11 AM
</pre>

By the way, you can run the code above in VSCode. Just install “Code Runner”. Save your file, and use CNTL-ALT-N to run it.

In the extension manager, you can choose options to auto-save the file before running it (otherwise it runs what is on disk, and not what is in memory). There is also an option to clear the output windows on each run.

Another trick of “Code Runner” is that you can highlight the lines of code you want to run, then press the CNTL-ALT-N hot key, and it just runs those lines of code.

The official way in VSCode to run an extension is to press F1, search for the extension. In this case, like for “run code” (not “code Runner”), and select it. But after a while, I’m sure you will start using the short-cut key.

I needed to update a database table with all the domains I had deleted back in January this year. There were actually about 32 of them, and I have only included a sample of 8 in the code below. I had a series of emails from GoDaddy, so I found it quickest to just enter the date/time of th eemail and the domain in NotePad++.

This was a one time script, so I didn’t even read the file, I just copied the data directly into my Powershell script, and put it in here doc. Would have only taken 1 minute or two to read the file with a “get-contents” though.

My goal was to generate a series of SQL update statements that I could copy into SSMS (SQL Studio Management Studio) and run there. Of course, we could do the updates from PowerShell, but as I said, this was a quick get-it-done one-time one-off task (hopefully).

After I ran the command in SQL, I realized two things. I need to trim the website names (some had an extra space on the end).
And I needed a Print statement so SQL could show me website. It shows the “rows affected = 0” or “rows affected = 1” for each statement. But when I ran them all at once, the ones that were misspelled were hard to find (I had to count how many from the top or bottom).

In this specific case, all the deletes were on the same date, so I didn’t re-type that into the input; rather, hard-coded in the SQL update statement in the PowerShell.

<pre>
11:58 learn-guitar-online-video.com 
11:58 freelearntoplayguitar.com 
11:58 learn-ultimate-guitar-online.com 
12:00 learntoplaysongguitar.com 
12:19 parismages.com
12:22 swc2009.co.za
12:33 venaproexposed.org
12:42 littleaccelerators.com
</pre>

<pre>
cls 
$deletedDomains = @"
11:58 learn-guitar-online-video.com 
11:58 freelearntoplayguitar.com 
11:58 learn-ultimate-guitar-online.com 
12:00 learntoplaysongguitar.com 
12:19 parismages.com
12:22 swc2009.co.za
12:33 venaproexposed.org
12:42 littleaccelerators.com
"@

#split based on the new line character 
$rows = $deletedDomains.Split("`n")

foreach ($row in $rows)
{
   #write-host "row=$row"
   $hhmm = $row.substring(0,5)
   $website = $row.substring(6).Trim()
   #Write-host "HHMM=$hhmm website=$website"
   $sql = "update SEOWebSite set seows_dateDeleted = '2020-01-19 $($hhmm)' where seows_name = '$website'";
   Write-Host "print 'updating: $website'" 
   Write-Host $sql 
}

</pre>

I just copied the resulting SQL from the output window, pasted it into SSMS, and ran it! Easy-Peasy!
One minosr issue is that it wraps if the row was over 80 characters, so I manually had to fix a few of those.

print 'updating: learn-guitar-online-video.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 11:58' where seows_name = 'learn-guitar-online-video.com'
print 'updating: freelearntoplayguitar.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 11:58' where seows_name = 'freelearntoplayguitar.com'
print 'updating: learn-ultimate-guitar-online.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 11:58' where seows_name = 'learn-ultimate-guitar-online.co
m'
print 'updating: learntoplaysongguitar.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:00' where seows_name = 'learntoplaysongguitar.com'
print 'updating: parismages.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:19' where seows_name = 'parismages.com'
print 'updating: swc2009.co.za'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:22' where seows_name = 'swc2009.co.za'
print 'updating: venaproexposed.org'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:33' where seows_name = 'venaproexposed.org'
print 'updating: littleaccelerators.com'
update SEOWebSite set seows_dateDeleted = '2020-01-19 12:42' where seows_name = 'littleaccelerators.com'

The following script can be scheduled as a “CRON JOB”. It looks at all files (optionally with a file mask) in a given directory, and sends them to a specific SFTP site.

This example uses a private key, and the name of that key file is passed as the -i argument on the stp command line.

There “here doc” from << EOF to the line starting with EOF, is used to pass STDIN (standard input) to the SFTP command. It keeps a simple log of all files sent by just using the "echo' command to append the date time, the script name, and the filename on the log.txt file. If you are not familiar with BASH scripts, everything between the "do" and the "done" is subject the for loop in the example below. The "for" loop iterates and returns all the files in the given directory name (in a variable called $filename).

#!/bin/bash
for filename in /home/nealwalters/downloads/*.csv; do 
   currentDate=`date` 
   echo "Loop: Filename=$filename" 
   echo "$currentDate SFTPUser3 Filename=$filename" >> /home/nealwalters/scripts/log.txt

   sftp -i /home/nealwalters/keys/SFTPUser1_Private.pem SFTPUser3@192.168.1.179 << EOF
      cd User3ToMyCompany
      put $filename
      exit
EOF
   echo "----------------------------------------------------------------"
done

From my 10 hour course: Udemy Course:SFTP Client Server Deep Dive with OpenSSH (both Unix and Windows) and BizTalk

Microsoft only supports XQuery via SQL Server. So what if you wanted to run a XQuery 1.0 command and save the resuilts to a disk file?

Saving to disk files can be a little tricky from SQL. So instead, we can just use SQL as our XQUery engine, run the query, and then save the results to disk.

This was a concept I came up with in my new XML course. Normally, I would just use the Saxonia .NET Transform.exe command (and it supports XQuery 3.0 and 3.1).

But if for some odd reason, we wanted to go “all Microsoft”, then the program below would do the job.
This may also just be interested to SQL developers, who didn’t know such a thing is even possible.

We use the “OPENROWSET” command to read an XML input file from the disk.
Xquery can also be run against XML Columns in an SQL table. But for purposes of learning, a person might want to run some of the simple XQuery examples that he or she finds on various websites. And of course, they all take XML files as input. You can download or copy/paste the XQuery and the data file(s), and then process them with the PowerShell below.

This of course assumes you have SQL server installed, and you have enough privileges to run the OPENROWSET command.

The entire SQL command is passed in a PowerShell “here doc” or “here string”. The timeout was increased to 180 seconds, because the default was not enough.

<pre>
cls 

$datasource = "server=server\instance;database=mydb;trusted_connection=true"
$datasource = "server=(local);database=master;trusted_connection=true"

#if not using Integrated Security, 
#you might want to pass user/pass in variables or prompt for them 
#$connectionString = 'User Id=' + $username + ';Password=' + $password + ';' + $datasource
$connectionString = $datasource
write-host $connectionString 
$connection = New-Object System.Data.SQLClient.SQLConnection($connectionString)
$connection.open()
Write-Host "SQL Connection Opened" 

$SQLScalarCommand = @'

SELECT Cast((SELECT BulkColumn FROM   OPENROWSET(BULK'c:\XMLClass\Shakespeare\Hamlet\hamlet.xml',SINGLE_CLOB) as myalias) as XML).query(
'
 <html><body>
 {
   for $act in //ACT
   return $act 
 }
 </body></html>
 ') 

'@

#Write-Host $SQLScalarCommand 

$Command = New-Object System.Data.SQLClient.SQLCommand 
$Command.Connection = $connection 
$Command.CommandText = $SQLScalarCommand 

$SQLResult = $Command.ExecuteScalar().ToString()
Write-Host ("Result=$SQLResult") 

$connection.close()  #not sure if this helps... 
                     #but in case you have more code after this... 
<pre>

You can also run the query above directly in SSMS (SQL Server Management Studio), and view the results there.
XQuery typically returns XML or HTML, but can also return any text.

>