need help with XML tag

Question

Post reply

need help with XML tag

Haryadi Santoso

Old Hand

Points: 334
More actions
May 5, 2011 at 7:54 pm

#249920

dear all,
please help me with this query that i still can't find the right solution
i have this query to generate a xml file:
declare @output xml;
set @output =
(
select 'CPAW3' as '@fileType',
(
select
upper(VCLUBCD) 'clubId',
upper(VCLUBNM) 'clubName',
convert(varchar(10), DCLUB, 120)+' 00:00:00' 'tglBerdiri',
upper(VCTYCLB) 'kotaDomisili',
upper(VPICCLB) 'ketuaClub',
upper(VJNSCLB) 'jenisClub',
upper(VTYPCLB)'tipeClub'
from master_club
for xml path('row'), type
)
for xml path('dataReq')
);
select @output
the result is:
<dataReq fileType="CPAW3">
<row>
<clubId>00001</clubId>
<clubName>HONDA TIGER MAILING LIST</clubName>
<tglBerdiri>1978-10-25 00:00:00</tglBerdiri>
<kotaDomisili>3101</kotaDomisili>
<ketuaClub>BUDI</ketuaClub>
<jenisClub>SPORT</jenisClub>
<tipeClub>TIGER</tipeClub>
</row>
<row>
<clubId>0002</clubId>
<clubName>SUZUKI CLUB</clubName>
<tglBerdiri>2002-10-05 00:00:00</tglBerdiri>
<ketuaClub>ADY</ketuaClub>
<jenisClub>BEBEK</jenisClub>
<tipeClub>SATRIA</tipeClub>
</row>
</dataReq>
as you can see that in second row, column "kotaDomilisi" is ommited from the XML, this is because the value is NULL.
need ur help, how i can deal with NULL column "kotaDomisili", so it can be written like
<kotaDomisili></kotaDomisili>??
i had used ELEMENTS XSINIL, to product XML tag like <kotaDomisili xsi:nil="true" />
but the system still won't accept it.
help me please...thx in advance...

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

ISNULL(upper(VCTYCLB), '')

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Haryadi Santoso Old Hand Points: 334 More actions · Answer 2

Hi Wayne,

I try ur suggestion but the result is like this:

<row>

<clubName>SUZUKI CLUB</clubName>

<jenisClub>BEBEK</jenisClub>

<tipeClub>SATRIA</tipeClub>

</row>

my client system still refuse it, it need to look like this ---> <kotaDomisili></kotaDomisili>

is there any solution?

WayneS (5/5/2011)
ISNULL(upper(VCTYCLB), '')

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

This seems to work

declare @master_club table(VCLUBCD varchar(100),VCLUBNM varchar(100), DCLUB datetime,

VCTYCLB varchar(100),VPICCLB varchar(100),VJNSCLB varchar(100),VTYPCLB varchar(100))

insert into @master_club

select '00001','HONDA TIGER MAILING LIST','19781025 00:00:00','3101','BUDI','SPORT','TIGER' union all

select '00002','SUZUKI CLUB','20021005 00:00:00',null,'ADY','BEBEK','SATRIA'

declare @output xml;

set @output =

(

select 'CPAW3' as '@fileType',

(

select

upper(VCLUBCD) 'clubId',

upper(VCLUBNM) 'clubName',

convert(varchar(10), DCLUB, 120)+' 00:00:00' 'tglBerdiri',

upper(VCTYCLB) 'kotaDomisili',

upper(VPICCLB) 'ketuaClub',

upper(VJNSCLB) 'jenisClub',

upper(VTYPCLB)'tipeClub'

from @master_club

for xml path('row'), type, ELEMENTS XSINIL

)

for xml path('dataReq')

);

select @output

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Haryadi Santoso Old Hand Points: 334 More actions · Answer 4

Hi Mark,

As i stated before, i had used ELEMENTS XSINIL, to product XML tag like <kotaDomisili xsi:nil="true" />

but the system still won't accept it. The system will accept XML tag like this : <kotaDomisili></kotaDomisili>, if the value is NULL

Mark-101232 (5/6/2011)
This seems to work
declare @master_club table(VCLUBCD varchar(100),VCLUBNM varchar(100), DCLUB datetime,
VCTYCLB varchar(100),VPICCLB varchar(100),VJNSCLB varchar(100),VTYPCLB varchar(100))
insert into @master_club
select '00001','HONDA TIGER MAILING LIST','19781025 00:00:00','3101','BUDI','SPORT','TIGER' union all
select '00002','SUZUKI CLUB','20021005 00:00:00',null,'ADY','BEBEK','SATRIA'
declare @output xml;
set @output =
(
select 'CPAW3' as '@fileType',
(
select
upper(VCLUBCD) 'clubId',
upper(VCLUBNM) 'clubName',
convert(varchar(10), DCLUB, 120)+' 00:00:00' 'tglBerdiri',
upper(VCTYCLB) 'kotaDomisili',
upper(VPICCLB) 'ketuaClub',
upper(VJNSCLB) 'jenisClub',
upper(VTYPCLB)'tipeClub'
from @master_club
for xml path('row'), type, ELEMENTS XSINIL
)
for xml path('dataReq')
);
select @output

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Haryadi Santoso (5/6/2011)
Hi Mark,
As i stated before, i had used ELEMENTS XSINIL, to product XML tag like <kotaDomisili xsi:nil="true" />
but the system still won't accept it. The system will accept XML tag like this : <kotaDomisili></kotaDomisili>, if the value is NULL
Mark-101232 (5/6/2011)
This seems to work
declare @master_club table(VCLUBCD varchar(100),VCLUBNM varchar(100), DCLUB datetime,
VCTYCLB varchar(100),VPICCLB varchar(100),VJNSCLB varchar(100),VTYPCLB varchar(100))
insert into @master_club
select '00001','HONDA TIGER MAILING LIST','19781025 00:00:00','3101','BUDI','SPORT','TIGER' union all
select '00002','SUZUKI CLUB','20021005 00:00:00',null,'ADY','BEBEK','SATRIA'
declare @output xml;
set @output =
(
select 'CPAW3' as '@fileType',
(
select
upper(VCLUBCD) 'clubId',
upper(VCLUBNM) 'clubName',
convert(varchar(10), DCLUB, 120)+' 00:00:00' 'tglBerdiri',
upper(VCTYCLB) 'kotaDomisili',
upper(VPICCLB) 'ketuaClub',
upper(VJNSCLB) 'jenisClub',
upper(VTYPCLB)'tipeClub'
from @master_club
for xml path('row'), type, ELEMENTS XSINIL
)
for xml path('dataReq')
);
select @output

Apologies, didn't read your post fully.

I don't think it's possible to generate

instead of

Would the system accept a single space instead of an empty string by using this

ISNULL(upper(VCTYCLB), ' ')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Haryadi Santoso Old Hand Points: 334 More actions · Answer 6

It won't either, i had run what WayneS is suggesting but it still doesn't work.

Mark-101232 (5/6/2011)
Haryadi Santoso (5/6/2011)
Hi Mark,
As i stated before, i had used ELEMENTS XSINIL, to product XML tag like <kotaDomisili xsi:nil="true" />
but the system still won't accept it. The system will accept XML tag like this : <kotaDomisili></kotaDomisili>, if the value is NULL
Mark-101232 (5/6/2011)
This seems to work
declare @master_club table(VCLUBCD varchar(100),VCLUBNM varchar(100), DCLUB datetime,
VCTYCLB varchar(100),VPICCLB varchar(100),VJNSCLB varchar(100),VTYPCLB varchar(100))
insert into @master_club
select '00001','HONDA TIGER MAILING LIST','19781025 00:00:00','3101','BUDI','SPORT','TIGER' union all
select '00002','SUZUKI CLUB','20021005 00:00:00',null,'ADY','BEBEK','SATRIA'
declare @output xml;
set @output =
(
select 'CPAW3' as '@fileType',
(
select
upper(VCLUBCD) 'clubId',
upper(VCLUBNM) 'clubName',
convert(varchar(10), DCLUB, 120)+' 00:00:00' 'tglBerdiri',
upper(VCTYCLB) 'kotaDomisili',
upper(VPICCLB) 'ketuaClub',
upper(VJNSCLB) 'jenisClub',
upper(VTYPCLB)'tipeClub'
from @master_club
for xml path('row'), type, ELEMENTS XSINIL
)
for xml path('dataReq')
);
select @output
Apologies, didn't read your post fully.
I don't think it's possible to generate
<kotaDomisili></kotaDomisili>
instead of
<kotaDomisili />
Would the system accept a single space instead of an empty string by using this
ISNULL(upper(VCTYCLB), ' ')