求教怎么将SQL查询语句输出为小弟我想要的XML格式

求教如何将SQL查询语句输出为我想要的XML格式

SQL code

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET 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>                 < Cell Index="3">                     <Data Type="String">ASDF</Data>                </Cell>           </Row>         </Table>       </Worksheet></Workbook>[/color]

求教

我可以通过SQL查询直接转换出下面红色的XML格式吗

[解决办法]
你参考这里的文章去写:

SQLServer2005 XML在T－SQL查询中的典型应用
http://topic.csdn.net/u/20081107/17/68aaf5a9-c596-4ab5-ae18-f3370b2ab35b.html

查看更多 下一篇