Need to remove certian section of XML from an XML column and place it in another XML column
Category: sql server xml
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>Sorry about the lame post this is my first one.
<Employee>
<EmployeeXrefCode>52995</EmployeeXrefCode>
<Election>
<EarnDeductType>Deduction</EarnDeductType>
<EarnDeductXrefCode>SRAD</EarnDeductXrefCode>
<EffectiveStartDate>2014-03-01</EffectiveStartDate>
<Amount>200.00</Amount>
</Election>
</Employee>
</EmployeeElection>
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...