Need to remove certian section of XML from an XML column and place it in another XML column

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

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