日期:2014-05-18  浏览次数:20460 次

求教如何将SQL查询语句输出为我想要的XML格式
SQL code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[YGXX](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [YGMC] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [YGXB] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [YGNL] [int] NULL,
 CONSTRAINT [PK_YGXX] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


SQL code
INSERT INTO [SMAPLE].[dbo].[YGXX]
           ([YGMC]
           ,[YGXB]
           ,[YGNL])
     VALUES
           ('张三'
           ,'男'
           ,22);
INSERT INTO [SMAPLE].[dbo].[YGXX]
           ([YGMC]
           ,[YGXB]
           ,[YGNL])
     VALUES
           ('王五'
           ,'男'
           ,22);
INSERT INTO [SMAPLE].[dbo].[YGXX]
           ([YGMC]
           ,[YGXB]
           ,[YGNL])
     VALUES
           ('李四'
           ,'男'
           ,22);


select 
row_number() over(order by id) as RowID,'1' as ColID,YGMC,
'2' as ColID,YGXB
from YGXX for XML path

XML code

<row>
  <RowID>1</RowID>
  <ColID>1</ColID>
  <YGMC>张三</YGMC>
  <ColID>2</ColID>
  <YGXB>男</YGXB>
</row>
<row>
  <RowID>2</RowID>
  <ColID>1</ColID>
  <YGMC>王五</YGMC>
  <ColID>2</ColID>
  <YGXB>女</YGXB>
</row>
<row>
  <RowID>3</RowID>
  <ColID>1</ColID>
  <YGMC>李四</YGMC>
  <ColID>2</ColID>
  <YGXB>男</YGXB>
</row>



第一种:
XML code

[color=#FF6600]<Row Index="3">
               <Cell Index="2">
                   <Data Type="Number">1234</Data>
               </Cell>
               <Cell Index="3">
                   <Data Type="Number">4567</Data>
               </Cell>
           </Row>
           <Row Index="4">
                <Cell Index="2">
                     <Data Type="String">asd</Data>
                </Cell> 
                < Cell Index="3">
                     <Data Type="String">ASDF</Data>
                </Cell>
           </Row>[/color]


第二种:
XML code

[color=#FF6600]<Workbook> 
       <DocumentProperties>
           <Created>2004-06-11T00:55:04Z</Created>
           <Version>03.1015</Version>
       </DocumentProperties>
       <Worksheet Name="第1页">
         <Table>
           <Row Index="3">
               <Cell Index="2">
                   <Data Type="Number">1234</Data>
               </Cell>
               <Cell Index="3">
                   <Data Type="Number">4567</Data>
               </Cell>
           </Row>
           <Row Index="4">
                <Cell Index="2">
                     <Data Type="String">asd</Data>
                </Cell> 
                &l