Question

Joe-Schmoe on Fri, 20 Jun 2014 20:35:11


<EmployeeElection>
  <Employee>
   <EmployeeXrefCode>52995</EmployeeXrefCode>
   <Election>
    <EarnDeductType>Earning</EarnDeductType>
    <EarnDeductXrefCode>10SCH CONTR</EarnDeductXrefCode>
    <EffectiveStartDate>2014-03-01</EffectiveStartDate>
    <PercentValue>10.00</PercentValue>
   </Election>
   <Election>
    <EarnDeductType>Deduction</EarnDeductType>
    <EarnDeductXrefCode>SRAD</EarnDeductXrefCode>
    <EffectiveStartDate>2014-03-01</EffectiveStartDate>
    <Amount>200.00</Amount>
   </Election>
  </Employee>
</EmployeeElection>

I need the above to look like the next code block based on the EarnDeductionType = Deduction and EarnDeductXrefCode In a list of values. 

 

<EmployeeElection>
	<Employee>
		<EmployeeXrefCode>52995</EmployeeXrefCode>
		<Election>
			<EarnDeductType>Earning</EarnDeductType>
			<EarnDeductXrefCode>10SCH CONTR</EarnDeductXrefCode>
			<EffectiveStartDate>2014-03-01</EffectiveStartDate>
			<PercentValue>10.00</PercentValue>
		</Election>
	</Employee>
</EmployeeElection>

And save/Insert the removed XML into another column in the same format.

 

<EmployeeElection>
  <Employee>
   <EmployeeXrefCode>52995</EmployeeXrefCode>
   <Election>
    <EarnDeductType>Deduction</EarnDeductType>
    <EarnDeductXrefCode>SRAD</EarnDeductXrefCode>
    <EffectiveStartDate>2014-03-01</EffectiveStartDate>
    <Amount>200.00</Amount>
   </Election>
  </Employee>
</EmployeeElection>
Sorry about the lame post this is my first one.




Sponsored



Replies

Joe-Schmoe on Fri, 20 Jun 2014 20:41:25


OK I need to do this with T-SQL

Erland Sommarskog on Fri, 20 Jun 2014 22:00:51


DECLARE @t TABLE (id int, x xml)
INSERT @t(id, x) VALUES(1, N'
<EmployeeElection>
    <Employee>
     <EmployeeXrefCode>52995</EmployeeXrefCode>
        <Election>
            <EarnDeductType>Earning</EarnDeductType>
            <EarnDeductXrefCode>10SCH CONTR</EarnDeductXrefCode>
            <EffectiveStartDate>2014-03-01</EffectiveStartDate>
            <PercentValue>10.00</PercentValue>
        </Election>
        <Election>
            <EarnDeductType>Deduction</EarnDeductType>
            <EarnDeductXrefCode>SRAD</EarnDeductXrefCode>
            <EffectiveStartDate>2014-03-01</EffectiveStartDate>
            <Amount>200.00</Amount>
        </Election>
    </Employee>
</EmployeeElection>')

INSERT @t(id, x)
   SELECT 2, T.c.query('Election[2]')
   FROM   @t
   CROSS  APPLY x.nodes('/EmployeeElection/Employee') AS T(c)
   WHERE  id = 1

UPDATE @t
SET    x.modify('delete /EmployeeElection/Employee/Election[2]')
WHERE  id = 1

SELECT * FROM @t

Joe-Schmoe on Fri, 20 Jun 2014 22:35:38


OK you are assuming (my bad) that the structure is always 2 elections and that the first stays and the second gets moved. This is helpful thank you I may be able to use this for another project.  And the requirement for saving the removed elections has been trashed

I have successfully removed the elections that are not supposed to be there, however some employees now have no elections and they are causing an error in import.

My efforts produced this format and now I need to delete Employees that have no elections or only have an employee XREF Code node and nothing else.  (Top employee stay the others need to be deleted.  Note**The employees that need to stay are intermingled between employees that need to go.

<EmployeeElection>
  <Employee>
	<EmployeeXrefCode>83573</EmployeeXrefCode>
	<Election>
	  <EarnDeductType>Deduction</EarnDeductType>
	  <EarnDeductXrefCode>RENTL</EarnDeductXrefCode>
	  <EffectiveStartDate>2014-03-01</EffectiveStartDate>
	  <Amount>575.92</Amount>
	</Election>
	<Election>
	  <EarnDeductType>Earning</EarnDeductType>
	  <EarnDeductXrefCode>FRENT</EarnDeductXrefCode>
	  <EffectiveStartDate>2014-03-01</EffectiveStartDate>
	  <Amount>100.00</Amount>
	</Election>
  </Employee>
</EmployeeElection>
<EmployeeElection>
  <Employee>
	<EmployeeXrefCode>54204</EmployeeXrefCode>
  </Employee>
</EmployeeElection>
<EmployeeElection>
  <Employee>
	<EmployeeXrefCode>54400</EmployeeXrefCode>
  </Employee>
</EmployeeElection>
<EmployeeElection>
  <Employee>
	<EmployeeXrefCode>54605</EmployeeXrefCode>
  </Employee>
</EmployeeElection>

I accomplished this much using the following MyColumn.modify

update @XML_TBL set
  XmlCol.modify('delete /EmployeeElectionImport/ElectionImport/EmployeeElection/Employee/Election[EarnDeductType = "Deduction" and (EarnDeductXrefCode = "ASRA" or EarnDeductXrefCode = "NSRA" or EarnDeductXrefCode = "NSRAD" or EarnDeductXrefCode = "SRAD" or EarnDeductXrefCode = "SRAWH" or EarnDeductXrefCode = "TIAAR")]')
  

Please also note that there is a set of nodes where the EarnDeductType is Earning and the codes are different so I cant just delete higher in the tree on the first pass.


Joe-Schmoe on Sat, 21 Jun 2014 00:12:37


OK so I used NotePad++ to get rid of the rest of the nodes - who's the man? Whatever it is Friday and I am out...