To update a column in XML Format Selecting data SQL Table using T-SQL

Question

I have data in a SQL Table and I want to query some columns of the existing data in XML Format(using For XML ) or any best practice and then update the XML FOrmat into a column in the same table with XML datatype .If you notice the example below , the Node SALARY is not a column name and its a constant value. ( In this sample there are 2 AMT columns as AMT1 and AMT2 which might vary as AMT3 sometimes) and accordingly the SALARY NODE Should also increment with the next number .If any one advice how to approach it would be of great help. The format of my data is :

EMPNAME  YEAR     MONTH AMT1     AMT2
smith     2013     jan  5000     6000
Ray       2014     feb  4000     5000
Jones     2013     apr  6000     3000

the XML format I want is :

<EMPLOYEE>
  <EMPNAME>Smith</EMPNAME>
  <SALARYDETAILS>
     <SALARY>1<SALARY>
     <AMOUNT>5000</AMOUNT>
     <SALARY>2<SALARY>
     <AMOUNT>6000</AMOUNT>
  </SALARYDETAILS>
</EMPLOYEE>
<EMPLOYEE>
      <EMPNAME>Ray</EMPNAME>
      <SALARYDETAILS>
         <SALARY>1<SALARY>
         <AMOUNT>4000</AMOUNT>
         <SALARY>2<SALARY>
         <AMOUNT>5000</AMOUNT>
      </SALARYDETAILS>
    </EMPLOYEE>

I tried the basic SQL like this but not sure how to add nodes in between which are not sql columns like 'SALARYDETAILS'

SELECT  EMPNAME ,AMT1,AMT2 FROM  EMPLOYEE
FOR XML RAW  (''), ROOT ('EMPLOYEE') - This SQL also gives error as Empty Tags cannot be passed

. Thanks Mikael, That was very helpful ,I could get the idea on specifying dummy nodes , but the requirement is , the number of amount nodes might vary depending on data and so the SALARY node will also depend on that .. like for some records it could be just one AMT1, some might have 3 AMT columns.. so it could vary and accordingly and so the query has to be built dynamically . I could even use a stored procedure . Thanks ..

Hi , Any idea how to concatenate 2 xml variables in a Stored Procedure.

You really should ask new questions (or two) instead of modifying this question. This one has an accepted answer and does not draw that much attention any more. 1. building the query dynamically is totally possible using metadata about the table and there are plenty of questions like that around here so do some searching and try it out. 2. How you concatenating XML is dependent on what the XML looks like before concatenation and how you want it to look like after so please ask a new question where you show some sample XML input and the desired output. — Mikael Eriksson
– Mikael Eriksson, Commented Sep 4, 2014 at 6:14
Thanks Mikael, I was able to get it from the Metadata using stored Procedure. I will put a new Question for concatenating it . — SNR
– SNR, Commented Sep 5, 2014 at 4:30

Mikael Eriksson · Accepted Answer · 2014-09-03 12:17:27Z

1

If I understand what you want you could just specify the salary nodes as constants in the query.

select E.EMPNAME,
       1      as 'SALARYDETAILS/SALARY',
       E.AMT1 as 'SALARYDETAILS/AMOUNT',
       2      as 'SALARYDETAILS/SALARY', 
       E.AMT2 as 'SALARYDETAILS/AMOUNT'
from EMPLOYEE as E
for xml path('EMPLOYEE')

answered Sep 3, 2014 at 12:17

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

To update a column in XML Format Selecting data SQL Table using T-SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related