Jonathan Fields
Reputation: 1
Read XML to Datatable dynamically
I have the following xml string that I am attempting to query into a datatable based on a dynamic set of columns. I do not have any control over the xml, it is being passed in from an external source.
XML:
<VmapMessage>
<MessageBody>
<ClaimId>346117125.01*BR</ClaimId>
<LastName>SMITH</LastName>
<FirstName>JOHN</FirstName>
<ClaimNumber>20222128369154</ClaimNumber>
<DOI>20131006</DOI>
<SOJ>OH</SOJ>
<EmailType>V2</EmailType>
<AdjusterName>STEPHAN WILLIAMS</AdjusterName>
<AdjusterId>TIWI</AdjusterId>
<AdjusterEmail>[email protected]</AdjusterEmail>
<SendAdjusterEmailAsHTML>T</SendAdjusterEmailAsHTML>
<AdjusterTimeZone>-8</AdjusterTimeZone>
<AdjusterDelegateName></AdjusterDelegateName>
<AdjusterDelegateId></AdjusterDelegateId>
<AdjusterDelegateEmail></AdjusterDelegateEmail>
<SendAdjusterDelegateEmailAsHTML></SendAdjusterDelegateEmailAsHTML>
<AdjusterDelegateTimeZone></AdjusterDelegateTimeZone>
<AdjusterDelegateStartDate></AdjusterDelegateStartDate>
<AdjusterDelegateEndDate></AdjusterDelegateEndDate>
<SupervisorEmail>[email protected]</SupervisorEmail>
<SendSupervisorEmailAsHTML>T</SendSupervisorEmailAsHTML>
<CarrierEmployee>N</CarrierEmployee>
<EncryptedSubId>232228520C6IB</EncryptedSubId>
<ChampionEmail>[email protected]</ChampionEmail>
<MasterCarrierInfo>
<MasterCarrierCode>COMP</MasterCarrierCode>
<CarrierName>COMP'S</CarrierName>
<VitalPointURL></VitalPointURL>
<EmployerCode>00000016</EmployerCode>
<OfficeCode>COMPREDCA</OfficeCode>
</MasterCarrierInfo>
<Details>
<Detail>
<Id>0005111*17228*DR3211</Id>
<ClaimId></ClaimId>
<RxNumber>0005073</RxNumber>
<RxDate>20140725</RxDate>
<DateEntered>20140917</DateEntered>
<FillNumber></FillNumber>
<ServiceType></ServiceType>
<NDC>72344299106</NDC>
<GPI>75100012333720</GPI>
<DrugClass>*SKELETAL MUSCLE RELAXANTS*</DrugClass>
<ServiceDescription>CYCLOBENZAPR CRE 20MG/GM</ServiceDescription>
<Quantity>60</Quantity>
<UnitOfMeasure></UnitOfMeasure>
<NumberOfRefills>0</NumberOfRefills>
<DaysSupply>30</DaysSupply>
<BillToCarrierAmount>0</BillToCarrierAmount>
<InvoiceAmount>188.77</InvoiceAmount>
<PrescriberNumber></PrescriberNumber>
<PrescriberName>SMITH, JOHN F MD</PrescriberName>
<PrescriberPhone></PrescriberPhone>
<DAW>0</DAW>
<BrandGeneric>MULTISOURCE</BrandGeneric>
<ICD9></ICD9>
<DiagnosisDesc></DiagnosisDesc>
<HCPC></HCPC>
<RejectCode>75S75</RejectCode>
<RejectReason>FM - Missing Pharmacy Telephone NumberüNDC NOT COVERED</RejectReason>
<PharmacyName>JOHN SMITH MD</PharmacyName>
<PharmacyPhysAddress1>6222 N SECOND ST</PharmacyPhysAddress1>
<PharmacyPhysAddress2></PharmacyPhysAddress2>
<PharmacyPhysCity>FRESNO</PharmacyPhysCity>
<PharmacyPhysState>CA</PharmacyPhysState>
<PharmacyPhysZip>937105446</PharmacyPhysZip>
<PharmacyPhone>9493342222</PharmacyPhone>
<StSub>0</StSub>
<Reg>0</Reg>
<MedStat>Y</MedStat>
<RegExcp>0</RegExcp>
<RejectCodes></RejectCodes>
<EmployerName>COMP'S WEST STORES, INC.</EmployerName>
<FirstBlock></FirstBlock>
<Blocks>
<Block>
<BlockCode>75*ndc</BlockCode>
<BlockReason>NDC NOT COVERED</BlockReason>
<Instance>1</Instance>
</Block>
<Block>
<BlockCode>76*ndc</BlockCode>
<BlockReason>NDC NOT KNOWN</BlockReason>
<Instance>2</Instance>
</Block>
</Blocks>
</Detail>
<Detail>
<Id>0005111*17228*DR3212</Id>
<ClaimId></ClaimId>
<RxNumber>0005074</RxNumber>
<RxDate>20140725</RxDate>
<DateEntered>20140917</DateEntered>
<FillNumber></FillNumber>
<ServiceType></ServiceType>
<NDC>72344299102</NDC>
<GPI>12345678901234</GPI>
<DrugClass>*SKELETAL MUSCLE RELAXANTS*</DrugClass>
<ServiceDescription>CYCLOBENZAPR CRE 20MG/GM</ServiceDescription>
<Quantity>60</Quantity>
<UnitOfMeasure></UnitOfMeasure>
<NumberOfRefills>0</NumberOfRefills>
<DaysSupply>30</DaysSupply>
<BillToCarrierAmount>0</BillToCarrierAmount>
<InvoiceAmount>188.77</InvoiceAmount>
<PrescriberNumber></PrescriberNumber>
<PrescriberName>SMITH, JOHN F MD</PrescriberName>
<PrescriberPhone></PrescriberPhone>
<DAW>0</DAW>
<BrandGeneric>MULTISOURCE</BrandGeneric>
<ICD9></ICD9>
<DiagnosisDesc></DiagnosisDesc>
<HCPC></HCPC>
<RejectCode>75S75</RejectCode>
<RejectReason>FM - Missing Pharmacy Telephone NumberNDC NOT COVERED</RejectReason>
<PharmacyName>JOHN SMITH MD</PharmacyName>
<PharmacyPhysAddress1>6222 N SECOND ST</PharmacyPhysAddress1>
<PharmacyPhysAddress2></PharmacyPhysAddress2>
<PharmacyPhysCity>FRESNO</PharmacyPhysCity>
<PharmacyPhysState>CA</PharmacyPhysState>
<PharmacyPhysZip>937105446</PharmacyPhysZip>
<PharmacyPhone>9493342222</PharmacyPhone>
<StSub>0</StSub>
<Reg>0</Reg>
<MedStat>Y</MedStat>
<RegExcp>0</RegExcp>
<RejectCodes></RejectCodes>
<EmployerName>COMP'S WEST STORES, INC.</EmployerName>
<FirstBlock></FirstBlock>
<Blocks>
<Block>
<BlockCode>75*ndc</BlockCode>
<BlockReason>NDC NOT COVERED</BlockReason>
<Instance>1</Instance>
</Block>
</Blocks>
</Detail>
</Details>
</MessageBody>
</VmapMessage>
Dynamic List of fields needed in output(This is being read in from DB and can change):
ParameterName ParameterPath
AdjusterEmail //MasterCarrierInfo//AdjusterEmail
EmpCode //MasterCarrierInfo//EmployerCode
SOJ //MessageBody//SOJ
DOI //MessageBody//DOI
GPI //MessageBody//Details//Detail//GPI
BlockCode //MessageBody//Details//Detail//Blocks//Block//BlockCode
I need to read this into a table that looks like this:
AdjusterEmail EmpCode SOJ DOI GPI BlockCode
[email protected] 00000016 OH 20131006 75100012333720 75*ndc
[email protected] 00000016 OH 20131006 75100012333720 76*ndc
[email protected] 00000016 OH 20131006 12345678901234 75*ndc
There can be multiple detail records per MessageBody and multiple Block records per Detail
I really have no idea where to start, I've tried a bunch of different things including linq(which I do not know) and can not figure this out...
Upvotes: 0
Views: 1535
Answers (2)
Thirisangu Ramanathan
Reputation: 614
It works:
XDocument xd = null;
using (StreamReader oReader = new StreamReader(xmlFilePath, Encoding.GetEncoding("ISO-8859-1")))
{
xd = XDocument.Load(oReader);
}
var records = from root in xd.Descendants("MessageBody")
from details in root.Elements("Details").Elements("Detail")
select new
{
AdjusterEmail = root.Element("AdjusterEmail").Value,
EmpCode = root.Element("MasterCarrierInfo").Element("EmployerCode").Value,
SOJ = root.Element("SOJ").Value,
DOI = root.Element("DOI").Value,
GPI = details.Element("GPI").Value,
BlockCode = details.Element("Blocks").Element("Block").Element("BlockCode").Value
};
Create Data table to hold records :
DataTable dt = new DataTable();
dt.Columns.Add("AdjusterEmail", typeof(string));
dt.Columns.Add("EmpCode", typeof(string));
dt.Columns.Add("SOJ", typeof(string));
dt.Columns.Add("DOI", typeof(string));
dt.Columns.Add("GPI", typeof(string));
dt.Columns.Add("BlockCode", typeof(string));
DataRow dr = null;
foreach (var readValue in records)
{
dr = dt.NewRow();
dr["AdjusterEmail"] = readValue.AdjusterEmail;
dr["EmpCode"] = readValue.EmpCode;
dr["SOJ"] = readValue.SOJ;
dr["DOI"] = readValue.DOI;
dr["GPI"] = readValue.GPI;
dr["BlockCode"] = readValue.BlockCode;
dt.Rows.Add(dr);
}
dt contains as below:
AdjusterEmail EmpCode SOJ DOI GPI BlockCode
[email protected] 00000016 OH 20131006 75100012333720 75*ndc
[email protected] 00000016 OH 20131006 75100012333720 76*ndc
[email protected] 00000016 OH 20131006 12345678901234 75*ndc
Upvotes: 0
Ashok Rathod
Reputation: 840
You can use simply below method and pass your xml file path as input
DataSet ds = new DataSet();
ds.ReadXml("Your File Path");
You would have data set which will contain respective dataset object for your xml.
Upvotes: 1