i have the following working code to create a specific formatted XML file from an XML string input.
var dc = XElement.Parse(InputXml);
var cd = ContextDate.ToString("yyyyMMdd");
var xc = XNamespace.Get("XmlCache");
var mp = XNamespace.Get("mx.MarketParameters");
var fx = XNamespace.Get("mx.MarketParameters.Forex");
var fxsp = XNamespace.Get("mx.MarketParameters.Forex.Spot");
var xdoc = new XDocument(
new XDeclaration("1.0", "utf-8", "no"),
new XElement(xc + "XmlCache", new XAttribute(XNamespace.Xmlns + "xc", xc),
new XAttribute(XNamespace.Xmlns + "mp", mp),
new XAttribute(XNamespace.Xmlns + "fx", fx),
new XAttribute(XNamespace.Xmlns + "fxsp", fxsp),
new XAttribute(xc + "action", "Update"),
new XElement(xc + "XmlCacheArea", new XAttribute(xc + "value", "MarketParameters"),
new XElement(mp + "nickName", new XAttribute(XNamespace.Xmlns + "mp", mp),
new XAttribute(xc + "value", MpNickName),
new XElement(mp + "date",
new XAttribute(xc + "value", cd),
new XElement(fx + "forex", new XAttribute(XNamespace.Xmlns + "fx", fx),
new XElement(fxsp + "spot",
new XAttribute(XNamespace.Xmlns + "fxsp", fxsp),
from myRow in dc.Elements("row").AsEnumerable()
group myRow by new { ccy = myRow.GetElementValue("Ccy") }
into myGroup
select
new XElement(fxsp + "pair",
new XAttribute(xc + "value", myGroup.Key.ccy),
from myRow in myGroup
select
new XElement(fxsp + "pair",
new XAttribute(xc + "value", myRow.GetElementValue("Identifier")),
new XAttribute(xc + "type", "Fields"),
new XElement(mp + "ask", new XAttribute(xc + "keyFormat", "N"), myRow.GetElementValue("Value")),
new XElement(mp + "bid", new XAttribute(xc + "keyFormat", "N"), myRow.GetElementValue("Value")),
new XElement(mp + "mid", new XAttribute(xc + "keyFormat", "N"), myRow.GetElementValue("Value")),
new XElement(mp + "quotation", new XAttribute(xc + "keyFormat", "C"),
myRow.GetElementValue("Identifier")))))))))));
But now i need to do a self join on the table to combine 2 rows to 1. The table contains 2 different ProductTypes which need to be joined by the Identifier column. So i use myRow for type A and shiftRow for type B I need to have the value of both Value columns in one row. To overcome the ambiguous column names i read i need to use join into as stated here LINQ to SQL: How to handle ambiguous column names when joining tables? But i cannot use a 'select new {t1A = t1.ColumnA, t2A = t2.ColumnA, t3A = t3.ColumnA }' as suggested inside my LINQ to XML. I tried below but it won't compile with commented line uncommented.
xdoc = new XDocument(
new XDeclaration("1.0", "utf-8", "no"),
new XElement(xc + "XmlCache", new XAttribute(XNamespace.Xmlns + "xc", xc),
new XAttribute(XNamespace.Xmlns + "mp", mp),
new XAttribute(XNamespace.Xmlns + "fx", fx),
new XAttribute(XNamespace.Xmlns + "fxsp", fxsp),
new XAttribute(xc + "action", "Update"),
new XElement(xc + "XmlCacheArea", new XAttribute(xc + "value", "MarketParameters"),
new XElement(mp + "nickName", new XAttribute(XNamespace.Xmlns + "mp", mp),
new XAttribute(xc + "value", MpNickName),
new XElement(mp + "date",
new XAttribute(xc + "value", cd),
new XElement(fx + "forex", new XAttribute(XNamespace.Xmlns + "fx", fx),
new XElement(fxsp + "spot",
new XAttribute(XNamespace.Xmlns + "fxsp", fxsp),
from myRow in
dc.Elements("row").AsEnumerable().Where(x => x.GetElementValue("ProductType").Equals("A"))
join shiftRow in
dc.Elements("row").AsEnumerable().Where(x => x.GetElementValue("ProductType").Equals("B")) on
myRow.GetElementValue("Identifier") equals shiftRow.GetElementValue("Identifier")
into joined
from shiftRow in joined.DefaultIfEmpty()
//new { t1A = myRow.GetElementValue("Value"), t2A = shiftRow.GetElementValue("Value") }
group myRow by new { ccy = myRow.GetElementValue("Ccy") }
into myGroup
select
new XElement(fxsp + "pair",
new XAttribute(xc + "value", myGroup.Key.ccy),
from myRow in myGroup
select
new XElement(fxsp + "pair",
new XAttribute(xc + "value", myRow.GetElementValue("Identifier")),
new XAttribute(xc + "type", "Fields"),
new XElement(mp + "ask", new XAttribute(xc + "keyFormat", "N"), myRow.GetElementValue("Value")),
new XElement(mp + "bid", new XAttribute(xc + "keyFormat", "N"), myRow.GetElementValue("Value")),
new XElement(mp + "mid", new XAttribute(xc + "keyFormat", "N"), myRow.GetElementValue("Value")),
new XElement(mp + "quotation", new XAttribute(xc + "keyFormat", "C"),
myRow.GetElementValue("Identifier")))))))))));
how to solve? i need to use the value of shiftRow after the group into clause which makes shiftRow not accessible anymore. Sorry for the lengthy code...
