以XML格式获取SQL结果 [英] Fetch SQL results in XML format

问题描述

你好朋友，
如何以XML格式检索SQL查询结果?我的意思是，每当我们在数据库(SQLServer，Oracle，MySql等)上触发查询时，它都会以XML格式返回结果.

实际上，我必须在ASP.Net Web应用程序中使用它，而方案是，我们不能编写诸如"XML Auto，Raw，Path"之类的查询.那么还有其他方法可以处理吗? br/>
谢谢.

Hello Friends,
How can retrieve SQL Query results in a XML format? I means, whenever we fired a Query on Database (either SQLServer, Oracle, MySql etc..) it returns the result in XML format.

actually I have to used it with my ASP.Net web Application and the scenario is , we can''t write query like ''XML Auto, Raw, Path'' etc. so is there any other way to handle this?.

Thanks.

推荐答案

DECLARE @Employee TABLE (id Bigint IDENTITY(1,1), Name NVARCHAR(50))
INSERT INTO @Employee (name) SELECT 'ABC'
INSERT INTO @Employee (name) SELECT 'DEF'
INSERT INTO @Employee (name) SELECT 'GHI'
INSERT INTO @Employee (name) SELECT 'JKL'
INSERT INTO @Employee (name) SELECT 'MNO'


select * from @Employee For XML Path('Employee')

>

结果将是

Result will be

<Employee>
  <id>1</id>
  <Name>ABC</Name>
</Employee>
<Employee>
  <id>2</id>
  <Name>DEF</Name>
</Employee>
<Employee>
  <id>3</id>
  <Name>GHI</Name>
</Employee>
<Employee>
  <id>4</id>
  <Name>JKL</Name>
</Employee>
<Employee>
  <id>5</id>
  <Name>MNO</Name>
</Employee>

select * from @Employee For XML RAW(''Employee'')

结果将是

Result will be

<Employee id="1" Name="ABC" />
<Employee id="2" Name="DEF" />
<Employee id="3" Name="GHI" />
<Employee id="4" Name="JKL" />
<Employee id="5" Name="MNO" />

select * from @Employee For XML AUTO

结果将是

Result will be

<_x0040_Employee id="1" Name="ABC" />
<_x0040_Employee id="2" Name="DEF" />
<_x0040_Employee id="3" Name="GHI" />
<_x0040_Employee id="4" Name="JKL" />
<_x0040_Employee id="5" Name="MNO" />

一旦将数据存储在DataSet中，就可以始终使用WriteXml将其转换为XML.我不想再次猜测您在做什么，但是如果您已经在SQL中查询了XML，为什么还要查询XML数据集?

Once you have your data in a DataSet, you can always use the WriteXml to get it into XML. I don''t want to second guess what you are doing, but why would you want to query the dataset in XML if you already queried it in your SQL?

这篇关于以XML格式获取SQL结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！