Trying to use column data as a field name for XML code

Question

Post reply

Trying to use column data as a field name for XML code

vls1

Valued Member

Points: 53
More actions
August 16, 2018 at 7:51 am

#360433

I would appreciate any assistance I can get with this problem. Thank you.
I have a dataset that contains only the elements that need to be updated in the application. For each username and year, I need to create the xml code to update the application. If the leave id is null its a new record.
The dataset looks like this:
username   leave_id_ai    ac_year     col      value vls1    165712136193 2013-2014   DTD_END   13 vls1       165712138241   2014-2015   DTD_END   13 vls1       165712140289   2015-2016   DTD_END   13 vls1    165712142337   2016-2017   DTD_END   13 vls1       165712144387   2017-2018   DTD_START 24 vls1       NULL 2018-2019   LEAVE     Leave of Absence vls1      NULL 2018-2019   DTM_START September
vls1       NULL 2018-2019   DTD_START 24
vls1       NULL 2018-2019   DTY_START 2017
vls1       NULL 2018-2019   DTM_END   July
vls1       NULL 2018-2019   DTD_END   9
vls1       NULL 2018-2019   DTY_END   2017
col is what the < > should be and value is the new data.
Here is a sample of the XML for all the elements.
<Data> <Record username="vls1"> <ADMIN> <AC_YEAR>2018-2019</AC_YEAR> <TERM_STATUS id=""> <LEAVE>Leave of Absence</LEAVE>     <DTM_START>September</DTM_START>     <DTD_START>24</DTD_START>     <DTY_START>2017</DTY_START>     <DTM_END>July</DTM_END>     <DTD_END>9</DTD_END>     <DTY_END>2017</DTY_END>    </TERM_STATUS>   </ADMIN>   </Record> </Data>
I know how to write the code to get the above but what I want to dis for the Term Status section, I want to use col as the <> and value and I don't know how to use col as the column name.
For the first row would look like this
<Data> <Record username="vls1"> <ADMIN> <AC_YEAR>2013-2014</AC_YEAR> <TERM_STATUS id="165712136193"/> <DTD_END>13</DTD_END> </TERM_STATUS> </ADMIN> </Record> </Data>
select value as col from ....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

can you provide DDL and sample data as well as what you're expected outcome should look like?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vls1 Valued Member Points: 53 More actions · Answer 2

Mike01 - Thursday, August 16, 2018 8:30 AM
can you provide DDL and sample data as well as what you're expected outcome should look like?

DDL:
username (nvarchar(20), null),
leave_id_ai (varchar(50), null),
ac_year (varchar(12), null),
col (varchar(9), not null),
value (varchar(20), null)

The row in the table will look like this:

<Data> <Record username="vls1"> <ADMIN> <AC_YEAR>2013-2014</AC_YEAR> <TERM_STATUS id="165712136193"/> <DTD_END>13</DTD_END> </TERM_STATUS> </ADMIN> </Record> </Data>

This would only update the DTD_END (col) element instead of the entire record.

andycadley SSCertifiable Points: 5296 More actions · Answer 3

Sample data would help, it's not really clear how all that XML relates to the table. Also is the list of possible columns something that can be known in advance?

vls1 Valued Member Points: 53 More actions · Answer 4

andycadley - Thursday, August 16, 2018 11:29 AM
Sample data would help, it's not really clear how all that XML relates to the table. Also is the list of possible columns something that can be known in advance?

The dataset looks like this:
username leave_id_ai     ac_year      col        value vls1     165712136193    2013-2014    DTD_END    13 vls1   165712138241    2014-2015    DTD_END    13 vls1   165712140289    2015-2016    DTD_END    13 vls1     165712142337    2016-2017    DTD_END    13 vls1   165712144387    2017-2018    DTD_START 24 vls1    NULL            2018-2019    LEAVE Leave of Absence vls1   NULL            2018-2019    DTM_START September
vls1   NULL          2018-2019    DTD_START 24
vls1    NULL          2018-2019    DTY_START 2017
vls1 NULL    2018-2019    DTM_END    July
vls1    NULL    2018-2019    DTD_END    9
vls1    NULL            2018-2019    DTY_END    2017

Bascially all want to know is how can I use col as the field name in a query

select value as col from view

andycadley SSCertifiable Points: 5296 More actions · Answer 5

drop table if exists #temp
create table #temp
(
username nvarchar(20) null,
leave_id_ai varchar(50) null,
ac_year varchar(12) null,
col varchar(9) not null,
value varchar(20) null
)

Insert Into #Temp(username,leave_id_ai,ac_year,col,value)
Values
('vls1','165712136193','2013-2014','DTD_END','13'),
('vls1','165712138241','2014-2015','DTD_END','13'),
('vls1','165712140289','2015-2016','DTD_END','13'),
('vls1','165712142337','2016-2017','DTD_END','13'),
('vls1','165712144387','2017-2018','DTD_START','24'),
('vls1',NULL,'2018-2019','LEAVE','Leave of Absence'),
('vls1',NULL,'2018-2019','DTM_START','September'),
('vls1',NULL,'2018-2019','DTD_START','24'),
('vls1',NULL,'2018-2019','DTY_START','2017'),
('vls1',NULL,'2018-2019','DTM_END','July'),
('vls1',NULL,'2018-2019','DTD_END','9'),
('vls1',NULL,'2018-2019','DTY_END','2017')

select
username As [@username],
ac_year As [ADMIN/AC_YEAR],
leave_id_ai as [ADMIN/TERM_STATUS/@id],
[DTD_START] As [ADMIN/DTD_START],
[DTD_END] As [ADMIN/DTD_END],
[LEAVE] As [ADMIN/LEAVE],
[DTM_START] As [ADMIN/DTM_START],
[DTM_END] As [ADMIN/DTM_END],
[DTY_START] As [ADMIN/DTY_START],
[DTY_END] As [ADMIN/DTY_END]
from #temp
pivot
(
Max(value) For col In ([DTD_START],[DTD_END],[LEAVE],[DTM_START],[DTM_END],[DTY_START],[DTY_END])
) PVT
For XML Path('Record'), Root('Data')