SQL Server Performance

need help to generate XML file

Discussion in 'SQL Server 2005 General Developer Questions' started by adyseven, May 5, 2011.

  1. adyseven New Member

    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...

    adyseven, May 5, 2011
    #2

  2. satya Moderator

    <P mce_keep="true">Did you map the columns at the metadata level for XML, here is the example about XML:</P><P>Here is an XML representation of the same table using a forest of elements to represent each table: </P><P mce_keep="true"><BR>&nbsp;</P><P><IMG height=214 alt="XML representation of the same table using a forest of elements to represent each table" src="http://www.stylusstudio.com/images/figures/sql_xml_xml_representation.gif" width=281> </P><P mce_keep="true"><BR>&nbsp;</P><P>These mappings are also defined on the metadata level. For instance, SQL/XML defines how the datatypes of SQL are represented in the equivalent XML Schema. Each SQL type is derived from an equivalent built-in W3C XML Schema type. Where needed, facets are used to represent constraints added to those of the base type:</P><P mce_keep="true"><BR>&nbsp;</P><P><IMG height=157 alt="SQL/XML defines how SQL datatypes&#13;&#10;are represented in the equivalent XML Schema" src="http://www.stylusstudio.com/images/figures/sql_xml_xml_schema.gif" width=347> </P><P mce_keep="true"><BR>&nbsp;</P><P>As mentioned above, there are two ways to represent null values. Suppose the City column may have null values. Here is a row in the Customer's table that represents a null value using the first strategy, a nilled element:</P><P mce_keep="true"><BR>&nbsp;</P><P><IMG height=114 alt="Representing nil values in XML" src="http://www.stylusstudio.com/images/figures/sql_xml_nil_value.gif" width=253> </P><P mce_keep="true"><BR>&nbsp;</P>
    satya, May 9, 2011
    #3

Share This Page