如何把数据库中类型为XML的列查询出来并以表格显示?

发布网友发布时间：2022-04-21 19:36

共2个回答

热心网友时间：2022-04-10 22:02

最近刚刚做这个：
public void BindData()
{

DataTable dt = InitTableForBindData();

if (ControlAppType == 2)
{
gv_RawDataList.DataSource = null;
gv_RawDataList.DataBind();

for (int int_row = gv_RawDataList.Columns.Count - 1; int_row > 2; int_row--)
{
gv_RawDataList.Columns.RemoveAt(int_row);
}
pnlSelectPhotos.Visible = false;
pnlSpecial.Visible = false;

//dt = BaseClass.GridViewToTable2.Instance.GridViewToDataTable(gv_RawDataList);
}

EditionWCF.t_edition t_Edition = editionClient.GetEditionById(EditionID);
List<Photo> PhotoList = new List<Photo>();
List<Special> SpecialList = new List<Special>();

//构造table的列
if ((t_Edition != null) && (t_Edition.ContentXML != null))
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(t_Edition.ContentXML.ToString());
XmlNode nodeRoot = doc.SelectSingleNode(@"Edition/Pages");

foreach (XmlNode node_Page in nodeRoot)
{
if (node_Page.Attributes[0].Value.ToString() == PageNum.ToString())
{
XmlNode node_Items = node_Page.SelectSingleNode(@"Items");
foreach (XmlNode node_item in node_Items)
{
if (node_item.Name == "Item")
{
XmlNode node_RawdataColumns = node_item.SelectSingleNode(@"RawdataColumns");
foreach (XmlNode node_RawdataColumn in node_RawdataColumns)
{
try
{
dt.Columns.Add(node_RawdataColumn.Attributes[0].Value, typeof(string));
}
catch { }
}

XmlNode node_UserColumns = node_item.SelectSingleNode(@"UserColumns");
foreach (XmlNode node_UserColumn in node_UserColumns)
{
try
{
dt.Columns.Add(node_UserColumn.Attributes[0].Value, typeof(string));
hidenUserDataCount.Value = (int.Parse(hidenUserDataCount.Value) + 1).ToString();
}
catch { }
}

}
}
}
}

}

///装载数据
if ((t_Edition != null) && (t_Edition.ContentXML != null))
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(t_Edition.ContentXML.ToString());
XmlNode nodeRoot = doc.SelectSingleNode(@"Edition/Pages");

foreach (XmlNode node_Page in nodeRoot)
{
if (node_Page.Attributes[0].Value.ToString() == PageNum.ToString())
{
XmlNode node_Items = node_Page.SelectSingleNode(@"Items");

foreach (XmlNode node_item in node_Items)
{
if (node_item.Name == "Item")
{

DataRow dr = dt.NewRow();
dr["SKU"] = "" + Server.HtmlDecode(node_item.Attributes["SKU"].Value) + "";
dr["Name"] = "" + Server.HtmlDecode(node_item.Attributes["Name"].Value) + "";
dr["Description"] = "" + Server.HtmlDecode(node_item.Attributes["Description"].Value) + "";
//dr["PriceRef"] = "" + Server.HtmlDecode(node_item.Attributes["PriceRef"].Value) + "";

XmlNode node_RawdataColumns = node_item.SelectSingleNode(@"RawdataColumns");
foreach (XmlNode node_RawdataColumn in node_RawdataColumns)
{
dr[node_RawdataColumn.Attributes[0].Value] = Server.HtmlDecode(node_RawdataColumn.InnerXml);
}

XmlNode node_UserColumns = node_item.SelectSingleNode(@"UserColumns");
foreach (XmlNode node_UserColumn in node_UserColumns)
{
dr[node_UserColumn.Attributes[0].Value] = Server.HtmlDecode(node_UserColumn.SelectSingleNode("Value").InnerXml);
}

XmlNode node_Photolinks = node_item.SelectSingleNode(@"Photolinks");
foreach (XmlNode node_Photolink in node_Photolinks)
{
Photo p = new Photo();
p.SKU = dr["SKU"].ToString();
p.PhotoID = "0";
p.PhotoPath = node_Photolink.InnerXml;
p.PhotoName = "0";
PhotoList.Add(p);
}

XmlNode node_Specials = node_item.SelectSingleNode(@"Specials");
foreach (XmlNode node_Special in node_Specials)
{
Special p = new Special();
p.SKU = dr["SKU"].ToString();
p.SpecialCode = node_Special.InnerXml;
SpecialList.Add(p);
}

dt.Rows.Add(dr);
}
}
}
}

}
//构造GridView列
foreach (DataColumn dc in dt.Columns)
{
BoundField bf = new BoundField();
bf.HeaderText = "" + dc.ColumnName + "";
bf.DataField = dc.ColumnName;
bf.HtmlEncode = false;
gv_RawDataList.Columns.Add(bf);
}

gv_RawDataList.DataSource = dt;
gv_RawDataList.DataBind();
SetGvLink();

gv_Hidden_ALLSpecials.DataSource = SpecialList;
gv_Hidden_ALLSpecials.DataBind();

gv_Hidden_Photos.DataSource = PhotoList;
gv_Hidden_Photos.DataBind();

BindChkListSpecial();
Bindgv_EachRawData_Photos();

SetGVPhotoButtonValue();
hidenOldGVColumnsCount.Value = gv_RawDataList.Columns.Count.ToString();

}

前台就一个GV

热心网友时间：2022-04-10 23:20

SQL:

declare @xml as xml;
SELECT @xml = col1 FROM TableName;
SELECT
T.x.value('./@name','varchar(100)') as Name,
T.x.value('./@age','int') as Age,
T.x.value('./@sex','int') as Sex
FROM @xml.nodes('/root/temp') AS T(x)

CS:

SqlConnection connection = new SqlConnection(YourConnectionString);
try
{
connection.Open();
SqlCommand command = connection.CreateCommand("declare @xml as xml;SELECT @xml = col1 FROM TableName;SELECT T.x.value('./@name','varchar(100)') as Name, T.x.value('./@age','int') as Age, T.x.value('./@sex','int') as Sex FROM @xml.nodes('/root/temp') AS T(x)");

.....
}
}