For XML Explicit

friends,
Below is the table "PCreditCardType"
I wanted to change the data of that table to the below mentioned XML format.
.
The solution should be through For XML Explicit or any SQL Operation...
Can you plz help me out'
Select * from PCreditCardType
CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
1 AMX American Express NULL
2 BCD BankCard NULL
3 DCB Diners Club NULL
4 DSC Discover NULL
5 ERC Eurocard NULL
6 JCB JCB NULL
7 MCD MasterCard NULL
8 VSA Visa NULL
<domainData name=" PCreditCardType " >
<cols>
<col name="CCTypeCode"/>
<col name="CCTypeName">
<col name="CCExpNumberOfYears">
</cols>
<rows>
<row> <![CDATA[AMX,American Express,NULL]]> </row>
<row> <![CDATA[BCD,BankCard,NULL]]> </row>
<row> <![CDATA[DCB,Diners Club,NULL]]> </row>
<row> <![CDATA[DSC,Discover,NULL]]> </row>
<row> <![CDATA[ERC,Eurocard,NULL]]> </row>
<row> <![CDATA[JCB,JCB,NULL]]> </row>
<row> <![CDATA[MCD,MasterCard,NULL]]> </row>
<row> <![CDATA[VSA,Visa,NULL]]> </row>
</rows>
</domainData>
Regards
Rao KRMYou can use the following query to dump XML rows section:
SELECT 1 as Tag,
NULL as Parent,
ISNULL(CCTypeCode, 'NULL') + ',' + ISNULL(CCTypeName, 'NULL') + ',' +
ISNULL(CCExpNumberOfYears, 'NULL') as [row!1!!cdata]
FROM PCreditCardType
FOR XML EXPLICIT
Then merge it with columns XML data in the application.
With best regards
Martin Rakhmanov
"For XML Explicit help wanted" <For XML Explicit help
wanted@.discussions.microsoft.com> wrote in message
news:73FCEF35-0CAE-4E51-B6DE-9A815BAF9DBA@.microsoft.com...
> friends,
> Below is the table "PCreditCardType"
> I wanted to change the data of that table to the below mentioned XML
format..
> The solution should be through For XML Explicit or any SQL Operation...
> Can you plz help me out'
> Select * from PCreditCardType
> CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
> 1 AMX American Express NULL
> 2 BCD BankCard NULL
> 3 DCB Diners Club NULL
> 4 DSC Discover NULL
> 5 ERC Eurocard NULL
> 6 JCB JCB NULL
> 7 MCD MasterCard NULL
> 8 VSA Visa NULL
>
> <domainData name=" PCreditCardType " >
> <cols>
> <col name="CCTypeCode"/>
> <col name="CCTypeName">
> <col name="CCExpNumberOfYears">
> </cols>
> <rows>
> <row> <![CDATA[AMX,American Express,NULL]]> </row>
> <row> <![CDATA[BCD,BankCard,NULL]]> </row>
> <row> <![CDATA[DCB,Diners Club,NULL]]> </row>
> <row> <![CDATA[DSC,Discover,NULL]]> </row>
> <row> <![CDATA[ERC,Eurocard,NULL]]> </row>
> <row> <![CDATA[JCB,JCB,NULL]]> </row>
> <row> <![CDATA[MCD,MasterCard,NULL]]> </row>
> <row> <![CDATA[VSA,Visa,NULL]]> </row>
> </rows>
> </domainData>
>
> Regards
> Rao KRM