Databases
StackOverflow2013
Postsdbo
POHow do I remove duplicate xml element values in an XDocument?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I remove duplicate xml element values in an XDocument?
    text
    copied!<p>I have the following code. I want to be able to check for and remove duplicate element values that are contained in the <code>StateRequestRecordGUID</code> element. Here is an example xml file that needs to be corrected.</p> <pre><code>&lt;?xml version="1.0"?&gt; &lt;StateSeparationRequestCollection xsi:schemaLocation="https://uidataexchange.org/schemas SeparationRequest.xsd" xmlns="https://uidataexchange.org/schemas" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt; &lt;StateSeparationRequest&gt; &lt;StateRequestRecordGUID&gt;30000000000000000000000000004000&lt;/StateRequestRecordGUID&gt; &lt;SSN&gt;999999999&lt;/SSN&gt; &lt;ClaimEffectiveDate&gt;2007-06-04&lt;/ClaimEffectiveDate&gt; &lt;ClaimNumber&gt;012345678901234567&lt;/ClaimNumber&gt; &lt;StateEmployerAccountNbr&gt;01234567890123456789&lt;/StateEmployerAccountNbr&gt; &lt;EmployerName&gt;JC PENNEY COMPANY INC 234567890123456789012345678901234567890123456789012345678901234567890123456789&lt;/EmployerName&gt; &lt;FEIN&gt;794741844&lt;/FEIN&gt; &lt;TypeofEmployerCode&gt;1&lt;/TypeofEmployerCode&gt; &lt;TypeofClaimCode&gt;1&lt;/TypeofClaimCode&gt; &lt;BenefitYearBeginDate&gt;2007-06-04&lt;/BenefitYearBeginDate&gt; &lt;RequestingStateAbbreviation&gt;ST&lt;/RequestingStateAbbreviation&gt; &lt;UIOfficeName&gt;Park Oaks 012345678901234&lt;/UIOfficeName&gt; &lt;UIOfficePhone&gt;6085264400&lt;/UIOfficePhone&gt; &lt;UIOfficeFax&gt;6085269394&lt;/UIOfficeFax&gt; &lt;ClaimantLastName&gt;SMITH-678901234567890123456789&lt;/ClaimantLastName&gt; &lt;OtherLastName&gt;WILLIAMS-901234567890123456789&lt;/OtherLastName&gt; &lt;ClaimantFirstName&gt;JOHN-56789012345678901234&lt;/ClaimantFirstName&gt; &lt;ClaimantMiddleInitial&gt;T&lt;/ClaimantMiddleInitial&gt; &lt;ClaimantSuffix&gt;Jr.-4567&lt;/ClaimantSuffix&gt; &lt;ClaimantJobTitle&gt;Manager-8901234567890123456789&lt;/ClaimantJobTitle&gt; &lt;ClaimantReportedFirstDayofWork&gt;2006-01-04&lt;/ClaimantReportedFirstDayofWork&gt; &lt;ClaimantReportedLastDayofWork&gt;2007-05-31&lt;/ClaimantReportedLastDayofWork&gt; &lt;WagesWeeksNeededCode&gt;WO&lt;/WagesWeeksNeededCode&gt; &lt;WagesNeededBeginDate&gt;2005-05-01&lt;/WagesNeededBeginDate&gt; &lt;WagesNeededEndDate&gt;2005-05-30&lt;/WagesNeededEndDate&gt; &lt;ClaimantSepReasonCode&gt;1&lt;/ClaimantSepReasonCode&gt; &lt;ClaimantSepReasonComments&gt;AAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAA&lt;/ClaimantSepReasonComments&gt; &lt;ReturntoWorkDate&gt;2010-01-01&lt;/ReturntoWorkDate&gt; &lt;RequestDate&gt;2006-06-07&lt;/RequestDate&gt; &lt;ResponseDueDate&gt;2006-06-17&lt;/ResponseDueDate&gt; &lt;FormNumber&gt;606C&lt;/FormNumber&gt; &lt;/StateSeparationRequest&gt; &lt;StateSeparationRequest&gt; &lt;StateRequestRecordGUID&gt;30000000000000000000000000004000&lt;/StateRequestRecordGUID&gt; &lt;SSN&gt;999999999&lt;/SSN&gt; &lt;ClaimEffectiveDate&gt;2007-06-04&lt;/ClaimEffectiveDate&gt; &lt;ClaimNumber&gt;012345678901234567&lt;/ClaimNumber&gt; &lt;StateEmployerAccountNbr&gt;01234567890123456789&lt;/StateEmployerAccountNbr&gt; &lt;EmployerName&gt;JC PENNEY COMPANY INC 234567890123456789012345678901234567890123456789012345678901234567890123456789&lt;/EmployerName&gt; &lt;FEIN&gt;794741844&lt;/FEIN&gt; &lt;TypeofEmployerCode&gt;1&lt;/TypeofEmployerCode&gt; &lt;TypeofClaimCode&gt;1&lt;/TypeofClaimCode&gt; &lt;BenefitYearBeginDate&gt;2007-06-04&lt;/BenefitYearBeginDate&gt; &lt;RequestingStateAbbreviation&gt;ST&lt;/RequestingStateAbbreviation&gt; &lt;UIOfficeName&gt;Park Oaks 012345678901234&lt;/UIOfficeName&gt; &lt;UIOfficePhone&gt;6085264400&lt;/UIOfficePhone&gt; &lt;UIOfficeFax&gt;6085269394&lt;/UIOfficeFax&gt; &lt;ClaimantLastName&gt;SMITH-678901234567890123456789&lt;/ClaimantLastName&gt; &lt;OtherLastName&gt;WILLIAMS-901234567890123456789&lt;/OtherLastName&gt; &lt;ClaimantFirstName&gt;JOHN-56789012345678901234&lt;/ClaimantFirstName&gt; &lt;ClaimantMiddleInitial&gt;T&lt;/ClaimantMiddleInitial&gt; &lt;ClaimantSuffix&gt;Jr.-4567&lt;/ClaimantSuffix&gt; &lt;ClaimantJobTitle&gt;Manager-8901234567890123456789&lt;/ClaimantJobTitle&gt; &lt;ClaimantReportedFirstDayofWork&gt;2006-01-04&lt;/ClaimantReportedFirstDayofWork&gt; &lt;ClaimantReportedLastDayofWork&gt;2007-05-31&lt;/ClaimantReportedLastDayofWork&gt; &lt;WagesWeeksNeededCode&gt;WO&lt;/WagesWeeksNeededCode&gt; &lt;WagesNeededBeginDate&gt;2005-05-01&lt;/WagesNeededBeginDate&gt; &lt;WagesNeededEndDate&gt;2005-05-30&lt;/WagesNeededEndDate&gt; &lt;ClaimantSepReasonCode&gt;1&lt;/ClaimantSepReasonCode&gt; &lt;ClaimantSepReasonComments&gt;AAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAA&lt;/ClaimantSepReasonComments&gt; &lt;ReturntoWorkDate&gt;2010-01-01&lt;/ReturntoWorkDate&gt; &lt;RequestDate&gt;2006-06-07&lt;/RequestDate&gt; &lt;ResponseDueDate&gt;2006-06-17&lt;/ResponseDueDate&gt; &lt;FormNumber&gt;606C&lt;/FormNumber&gt; &lt;/StateSeparationRequest&gt; &lt;/StateSeparationRequestCollection&gt; </code></pre> <p>Here would be the corrected xml:</p> <pre><code>&lt;?xml version="1.0"?&gt; &lt;StateSeparationRequestCollection xsi:schemaLocation="https://uidataexchange.org/schemas SeparationRequest.xsd" xmlns="https://uidataexchange.org/schemas" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt; &lt;StateSeparationRequest&gt; &lt;StateRequestRecordGUID&gt;30000000000000000000000000004000&lt;/StateRequestRecordGUID&gt; &lt;SSN&gt;999999999&lt;/SSN&gt; &lt;ClaimEffectiveDate&gt;2007-06-04&lt;/ClaimEffectiveDate&gt; &lt;ClaimNumber&gt;012345678901234567&lt;/ClaimNumber&gt; &lt;StateEmployerAccountNbr&gt;01234567890123456789&lt;/StateEmployerAccountNbr&gt; &lt;EmployerName&gt;JC PENNEY COMPANY INC 234567890123456789012345678901234567890123456789012345678901234567890123456789&lt;/EmployerName&gt; &lt;FEIN&gt;794741844&lt;/FEIN&gt; &lt;TypeofEmployerCode&gt;1&lt;/TypeofEmployerCode&gt; &lt;TypeofClaimCode&gt;1&lt;/TypeofClaimCode&gt; &lt;BenefitYearBeginDate&gt;2007-06-04&lt;/BenefitYearBeginDate&gt; &lt;RequestingStateAbbreviation&gt;ST&lt;/RequestingStateAbbreviation&gt; &lt;UIOfficeName&gt;Park Oaks 012345678901234&lt;/UIOfficeName&gt; &lt;UIOfficePhone&gt;6085264400&lt;/UIOfficePhone&gt; &lt;UIOfficeFax&gt;6085269394&lt;/UIOfficeFax&gt; &lt;ClaimantLastName&gt;SMITH-678901234567890123456789&lt;/ClaimantLastName&gt; &lt;OtherLastName&gt;WILLIAMS-901234567890123456789&lt;/OtherLastName&gt; &lt;ClaimantFirstName&gt;JOHN-56789012345678901234&lt;/ClaimantFirstName&gt; &lt;ClaimantMiddleInitial&gt;T&lt;/ClaimantMiddleInitial&gt; &lt;ClaimantSuffix&gt;Jr.-4567&lt;/ClaimantSuffix&gt; &lt;ClaimantJobTitle&gt;Manager-8901234567890123456789&lt;/ClaimantJobTitle&gt; &lt;ClaimantReportedFirstDayofWork&gt;2006-01-04&lt;/ClaimantReportedFirstDayofWork&gt; &lt;ClaimantReportedLastDayofWork&gt;2007-05-31&lt;/ClaimantReportedLastDayofWork&gt; &lt;WagesWeeksNeededCode&gt;WO&lt;/WagesWeeksNeededCode&gt; &lt;WagesNeededBeginDate&gt;2005-05-01&lt;/WagesNeededBeginDate&gt; &lt;WagesNeededEndDate&gt;2005-05-30&lt;/WagesNeededEndDate&gt; &lt;ClaimantSepReasonCode&gt;1&lt;/ClaimantSepReasonCode&gt; &lt;ClaimantSepReasonComments&gt;AAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAA&lt;/ClaimantSepReasonComments&gt; &lt;ReturntoWorkDate&gt;2010-01-01&lt;/ReturntoWorkDate&gt; &lt;RequestDate&gt;2006-06-07&lt;/RequestDate&gt; &lt;ResponseDueDate&gt;2006-06-17&lt;/ResponseDueDate&gt; &lt;FormNumber&gt;606C&lt;/FormNumber&gt; &lt;/StateSeparationRequest&gt; &lt;/StateSeparationRequestCollection&gt; int TotalCount = ssrcWrapper.EmployerTPASeparationRequestCollection.EmployerTPASeparationRequest.Count(); string ssrcWrapperString = XmlSerializerUtils.SerializeToXMLstring(ssrcWrapper.EmployerTPASeparationRequestCollection); System.IO.StringReader myStringReader = new System.IO.StringReader(ssrcWrapperString); XmlReader xmlreader = XmlReader.Create(myStringReader); xmlreader.MoveToContent(); for (int i = 0; i &lt; TotalCount; i++) { SqlConnection conn4 = new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=test_BdbCSSQL01;Persist Security Info=False;Integrated Security=SSPI;"); conn4.Open(); string sql = "SELECT * FROM SIDESStagingIN"; SqlDataAdapter da = new SqlDataAdapter(sql, conn4); DataTable dt = new DataTable(); da.Fill(dt); DataRow dr; dr = dt.NewRow(); dt.Rows.Add(dr); XDocument doc = XDocument.Load(xmlreader); XNamespace ns = "https://uidataexchange.org/schemas"; var node = doc.Descendants(ns + "EmployerTPASeparationRequest"); var node2 = node.ElementAt(i); string _StateRequestRecordGUID = ""; foreach (var element in node2.Elements()) { if (element.Name.LocalName == "StateRequestRecordGUID") { _StateRequestRecordGUID = element.Value; } if (element.Name.LocalName == "AttachmentOccurrence") { //ZJR: TODO: Create new XDoc and write values to dbo.SIDESAttachmentIN SqlConnection conn5 = new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=test_BdbCSSQL01;Persist Security Info=False;Integrated Security=SSPI;"); conn5.Open(); string sql2 = "SELECT * FROM SIDESAttachmentIN"; SqlDataAdapter da2 = new SqlDataAdapter(sql2, conn5); DataTable dt2 = new DataTable(); da2.Fill(dt2); DataRow dr2; dr2 = dt2.NewRow(); dt2.Rows.Add(dr2); dr2["AttachmentID"] = _StateRequestRecordGUID; var attachmentNode = doc.Descendants(ns + "AttachmentOccurrence"); foreach (var attachmentElement in attachmentNode.Elements()) { dr2[attachmentElement.Name.LocalName] = attachmentElement.Value; } dr["AttachmentID"] = _StateRequestRecordGUID; SqlCommandBuilder sb2 = new SqlCommandBuilder(da2); da2.Update(dt2); if (conn5 != null) { conn5.Close(); } } if (dr.Table.Columns.Contains(element.Name.LocalName)) { dr[element.Name.LocalName] = element.Value; } } SqlCommandBuilder sb = new SqlCommandBuilder(da); da.Update(dt); if (conn4 != null) { conn4.Close(); } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload