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 KRM
You 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