SQLServer和XML的内容介绍

FOR XML语法
该字句的语法如下所示：
[FOR{XML{RAW|AUTO|EXPLICIT}[,XMLDATA][,ELEMENTS][,BINARY BASE64]}]

FOR XML 模式使用 FORXML三种模式
SET ROWCOUNT 3
SELECT Orders.OrderID, Orders.OrderDate, ProductID FROM Orders,[Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
ORDER BY Orders.OrderID FOR XML RAW

SET ROWCOUNT 3
SELECT Orders.OrderID, Orders.OrderDate,ProductID FROM Orders, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
ORDER BY Orders.OrderID FOR XML AUTO

SELECT 1 AS Tag,
0 AS Parent,
RegionID AS [Region!1!RegionIDAsAttrbute],
RegionID AS [Region!1!RegionIDAsElement!element]
FROM Region
FOR XML EXPLICIT

FOR XML参数
ELEMENTS—只能应用于FOR XML AUTO查询，它指定为每列所返回的列值在XML文档中以一个元素，而不是作为一个属性。
BINARY BASE64—该选项使XML文档中的二进制数据被表示为BINARY BASE64编码。这种二进制数据一般都存在BINARY、BARBINARY或IMAGE类型的列中。
XMLDATA:该选项为FORXML查询产生的XML文档产生一个XML数据模式。该模式是XML文档的预定义格式。

SELECT Orders.OrderID, Orders.OrderDate,ProductID
FROM Orders, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID ORDER BY Orders.OrderID
FOR XML AUTO,ELEMENTS

SELECT FirstName, LastName, Photo
FROM Employees
ORDER BY LastName, FirstName
FOR XML RAW, BINARY BASE64

SELECT Region.RegionID,TerritoryID FROM Region,Territories
WHERE Region.RegionID = Territories.RegionID
ORDER BY TerritoryID
FOR XML AUTO,XMLDATA


private void Page_Load(object sender, System.EventArgs e)
{
Bind();
}
private void Bind()
{
SqlConnection con = new SqlConnection(strConnection);
SqlCommand com = new SqlCommand( "Select * from Region",con);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
dgShow.DataSource = ds;
dgShow.DataBind();
}

private void btnInsert_Click(object sender, System.EventArgs e)
{
SqlConnection con = new SqlConnection(strConnection);
SqlCommand openXMLCommand = new SqlCommand();
SqlParameter xmlDocParm = null;
string strXMLDoc =
"<Top>" +
"<Region RegionID=/"11/" RegionDescription=/"UpTown/"/>" +
"<Region RegionID=/"22/" RegionDescription=/"DownTown/"/>" +
"</Top>";
con.Open();
openXMLCommand.Connection = con;
openXMLCommand.CommandText = "RegionInsert";
openXMLCommand.CommandType = CommandType.StoredProcedure;
xmlDocParm = openXMLCommand.Parameters.Add("@xmlDoc",
SqlDbType.NVarChar,
4000);
xmlDocParm.Value = strXMLDoc;
openXMLCommand.ExecuteNonQuery();
con.Close();
Bind();
}

private void btnDelete_Click(object sender, System.EventArgs e)
{
SqlConnection con = new SqlConnection(strConnection);
SqlCommand openXMLCommand = new SqlCommand();
SqlParameter xmlDocParm = null;
string strXMLDoc =
"<Top>" +
"<Region RegionID=/"11/" RegionDescription=/"UpTown/"/>" +
"<Region RegionID=/"22/" RegionDescription=/"DownTown/"/>" +
"</Top>";
con.Open();
openXMLCommand.Connection = con;
openXMLCommand.CommandText = "RegionDelete";
openXMLCommand.CommandType = CommandType.StoredProcedure;
xmlDocParm = openXMLCommand.Parameters.Add("@xmlDoc",
SqlDbType.NVarChar,4000);
xmlDocParm.Value = strXMLDoc;
openXMLCommand.ExecuteNonQuery();
Bind();
}

private void btnUpdate_Click(object sender, System.EventArgs e)
{
SqlConnection con = new SqlConnection(strConnection);
SqlCommand openXMLCommand = new SqlCommand();
SqlParameter xmlDocParm = null;
string strXMLDoc =
"<Top>" +
"<Region RegionID=/"11/" RegionDescription=/"UpTown/"/>" +
"<Region RegionID=/"22/" RegionDescription=/"DownTown/"/>" +
"</Top>";
con.Open();
openXMLCommand.Connection = con;
openXMLCommand.CommandText = "RegionUpdate";
openXMLCommand.CommandType = CommandType.StoredProcedure;
xmlDocParm = openXMLCommand.Parameters.Add("@xmlDoc",
SqlDbType.NVarChar,4000);
xmlDocParm.Value = strXMLDoc.Replace("Town", "City");
openXMLCommand.ExecuteNonQuery();
Bind();
}


OPEN XML和ADO.NET
void ShowOffForXMLRaw()
{
try
{
string strQuery =
"SELECT FirstName, LastName " +
"FROM Employees " +
"ORDER BY LastName, FirstName " +
"FOR XML RAW, XMLDATA";
string strConnection =
"UID=sa;PWD=12345678;DATABASE=northwind;SERVER=(local);";
SqlCommand forXMLCommand =new SqlCommand(strQuery,new SqlConnection(strConnection));
DataSet ds = new DataSet();
forXMLCommand.Connection.Open();
ds.ReadXml(forXMLCommand.ExecuteXmlReader(),
XmlReadMode.Fragment);
ds.WriteXml(Server.MapPath("DemoForXMLRaw.xml"));
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
}

void ShowOffForXMLAuto()
{
try
{
string strConnection ="UID=sa;PWD=12345678;DATABASE=northwind;SERVER=(local);";
SqlConnection sqlConnection = new SqlConnection(strConnection);
sqlConnection.Open();
SqlCommand forXMLCommand = new SqlCommand("RegionTerritory");
forXMLCommand.Connection = sqlConnection;
forXMLCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
ds.ReadXml(forXMLCommand.ExecuteXmlReader(),
XmlReadMode.Fragment);
ds.WriteXml(Server.MapPath("DemoForXMLAuto.xml"));
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
}
private void btnRAW_Click(object sender, System.EventArgs e)
{
ShowOffForXMLRaw();
}
private void btnAUTO_Click(object sender, System.EventArgs e)
{
ShowOffForXMLAuto();
}

SQLXML和ADO.NET
private void btn1_Click(object sender, System.EventArgs e)
{
StreamReader s;
SqlXmlParameter p;
string strCon = "provider=sqloledb;server=(local);database=pubs;uid=sa;pwd=111";
SqlXmlCommand cmd = new SqlXmlCommand(strCon);
cmd.CommandText = "select * from authors where au_lname= ? "+"FOR XML AUTO";
p = cmd.CreateParameter();
p.Value = "Ringer";
s = new StreamReader(cmd.ExecuteStream());
string strOut = s.ReadToEnd();
StreamWriter sw = new StreamWriter(Server.MapPath("Result.txt"));
sw.Write(strOut);
sw.Close();
}

private void btn2_Click(object sender, System.EventArgs e)
{
SqlXmlParameter p;
string strCon = "provider=sqloledb;server=(local);database=pubs;uid=sa;pwd=111";
SqlXmlCommand cmd = new SqlXmlCommand(strCon);
cmd.CommandText = "select * from authors where au_lname= ? "+"FOR XML AUTO";
p = cmd.CreateParameter();
p.Value = "Ringer";
cmd.ClientSideXml = false;
StreamWriter sw = new StreamWriter(Server.MapPath("Result.txt"));
cmd.ExecuteToStream(sw.BaseStream);
sw.Close();
}


直接从数据库获取数据
private void Page_Load(object sender, System.EventArgs e)
{
connString = System.Configuration.ConfigurationSettings.AppSettings[ "NorthwindConnString" ]; //从web.config中读取数据库连接信息(下面称此句为“数据库连接语句”)
string sqlSelect;
sqlSelect = "SELECT * FROM Employees for xml auto, elements";
SqlXmlCommand sqlXmlCommand = new SqlXmlCommand(connString);
sqlXmlCommand.CommandText = sqlSelect;
sqlXmlCommand.ClientSideXml = true;
sqlXmlCommand.XslPath = Server.MapPath("EmployeeTransform.xslt");
sqlXmlCommand.RootTag = "NewDataSet";
sqlXmlCommand.ExecuteToStream(Response.OutputStream);
Response.End();
}

显示图片
using System.Drawing.Drawing2D;
using System.Data.SqlClient;

string connString;
private void Page_Load(object sender, System.EventArgs e)
{
string employeeID = Request.QueryString["EmployeeID"];
if(employeeID == string.Empty || employeeID == null)
{
return;
}
byte[] imgByteArray = GetImage(employeeID);
Response.ContentType="image/jpeg";
int offset = 78;
//读取图片并作图片格式转换，原图是BMP图，在网页中显示使用JPG图片，同时使图片感官效果优化：
System.IO.MemoryStream mstream = new System.IO.MemoryStream ();
System.IO.MemoryStream stream = new System.IO.MemoryStream ();
mstream.Write(imgByteArray, offset, imgByteArray.Length - offset);
System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(mstream);
Bitmap bmpTmp = new Bitmap(bmp.Width/2, bmp.Height/2);//尺寸缩小为50%
Graphics g = Graphics.FromImage(bmpTmp);
g.CompositingQuality = CompositingQuality.HighQuality;
g.InterpolationMode = InterpolationMode.HighQualityBicubic;
Rectangle destRect = new Rectangle(0, 0, bmpTmp.Width, bmpTmp.Height);
Rectangle srcRect = new Rectangle(0, 0, bmp.Width, bmp.Height);
g.DrawImage(bmp, destRect, srcRect, GraphicsUnit.Pixel);
bmpTmp.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg );
g.Dispose();
bmp.Dispose();
byte[] byteReturn = stream.ToArray();
mstream.Close();
stream.Close();
Response.OutputStream.Write(byteReturn, 0, byteReturn.Length);
Response.End();
}
private byte[] GetImage(string employeeID)
{
connString = System.Configuration.ConfigurationSettings.AppSettings[ "Connstring" ];
try
{
SqlConnection conn = new SqlConnection(connString);
string sqlSelect = "SELECT Photo FROM Employees WHERE EmployeeID = " + employeeID;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sqlSelect;
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if(dr.Read())
{
if(dr[0] != null)
{
byte[] byteImg = (byte[])dr[0];
return byteImg;
}
}
}
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
return null;
}