MS SQL XML - sql xml query to get empty data tag

Author

sql xml query to get empty data tag

martin1

2006-11-28, 7:23 pm

Hi, All,

I want to generate empty data tag, for example, the table1 has 2 fileds
called field1 and field2, the field1 has data there but field2 no data, when
creating xml fiel using query:

select field1, field2 from table1 for xml auto, elements

it creates xml file without empty elment tag like this:

<Table1>
<field1> 23 <field1>
</Table1>

But I want field2 xml tag <field2 /> show in the file like

<Table1>
<field1> 23 <field1>
<field2 />
</Table1>

That means <field2 /> tell no data, but I want the xml tag display in the
file.
can anyone know how to generate empty data xml tag using above sql xml query
or another solution?

Thanks in advance
Martin

Peter W. DeBetta

2006-11-29, 7:19 pm

What version of SQL Server are you using?

In 2005, you can use XSINIL to get something comparable:

SELECT Field1, Field2
FROM Table1
FOR XML AUTO, ELEMENTS XSINIL

which would return

<Table1>
<field1>23<field1>
<field2 xsi:nil="true" />
</Table1>

when Field2 is NULL...

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"martin1" <martin1@discussions.microsoft.com> wrote in message
news:B1F27684-92AB-4582-A06E- CD202DF6387A@microso
ft.com...
> Hi, All,
>
> I want to generate empty data tag, for example, the table1 has 2 fileds
> called field1 and field2, the field1 has data there but field2 no data,
> when
> creating xml fiel using query:
>
> select field1, field2 from table1 for xml auto, elements
>
> it creates xml file without empty elment tag like this:
>
> <Table1>
> <field1> 23 <field1>
> </Table1>
>
> But I want field2 xml tag <field2 /> show in the file like
>
> <Table1>
> <field1> 23 <field1>
> <field2 />
> </Table1>
>
> That means <field2 /> tell no data, but I want the xml tag display in the
> file.
> can anyone know how to generate empty data xml tag using above sql xml
> query
> or another solution?
>
> Thanks in advance
> Martin
>

martin1

2006-11-30, 7:19 pm

I use SQL Server 2000. any solution about it?

Thanks,
Martin

"Peter W. DeBetta" wrote:

> What version of SQL Server are you using?
>
> In 2005, you can use XSINIL to get something comparable:
>
> SELECT Field1, Field2
> FROM Table1
> FOR XML AUTO, ELEMENTS XSINIL
>
> which would return
>
> <Table1>
> <field1>23<field1>
> <field2 xsi:nil="true" />
> </Table1>
>
> when Field2 is NULL...
>
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "martin1" <martin1@discussions.microsoft.com> wrote in message
> news:B1F27684-92AB-4582-A06E- CD202DF6387A@microso
ft.com...
>
>
>

Han

2006-12-01, 7:17 pm

Hello martin1

I think you want isNull function.

select isNull(col, 'value-for-null') from T for XML ...

"martin1" <martin1@discussions.microsoft.com> wrote in message
news:F4F7A1FB-EFB8-4F3E-A688- 7DD68DAC879A@microso
ft.com...[color=darkred]
>I use SQL Server 2000. any solution about it?
>
> Thanks,
> Martin
>
> "Peter W. DeBetta" wrote:
>

martin1

2006-12-01, 7:17 pm

Thank you. Han. it is helpful.

But if field datetype is date, it is not work. can you tell what is value
for null date?

Thanks,
Martin

"Han" wrote:

> Hello martin1
>
> I think you want isNull function.
>
> select isNull(col, 'value-for-null') from T for XML ...
>
> "martin1" <martin1@discussions.microsoft.com> wrote in message
> news:F4F7A1FB-EFB8-4F3E-A688- 7DD68DAC879A@microso
ft.com...
>
>
>

Han

2006-12-02, 5:14 am

You can feed any value including empty string. However you need to convert
the datetime type to string BEFORE isnull().

create table t1 (d smalldatetime)
go
insert t1 values(getdate())
insert t1 values(null)

go

select isnull(convert(varch
ar(max), d, 112), '') 'd' from t1 for xml path

"martin1" <martin1@discussions.microsoft.com> wrote in message
news:82092552-B398-4BD3-A812- B2774CE7ACAA@microso
ft.com...[color=darkred]
> Thank you. Han. it is helpful.
>
> But if field datetype is date, it is not work. can you tell what is value
> for null date?
>
> Thanks,
> Martin
>
> "Han" wrote:
>

martin1

2006-12-04, 7:18 pm

Thank you again. Han. it is work now

"Han" wrote:

> You can feed any value including empty string. However you need to convert
> the datetime type to string BEFORE isnull().
>
> create table t1 (d smalldatetime)
> go
> insert t1 values(getdate())
> insert t1 values(null)
>
> go
>
> select isnull(convert(varch
ar(max), d, 112), '') 'd' from t1 for xml path
>
> "martin1" <martin1@discussions.microsoft.com> wrote in message
> news:82092552-B398-4BD3-A812- B2774CE7ACAA@microso
ft.com...
>
>
>

Sponsored Links