If you search this blog you will find several similar and some more detailed versions of this
Use Automart
Select * from Customer.AutoService
Insert into Customer.AutoService(Servicename, ServicePrice, ServiceDescription)
values('Pinstriping', '10000.00',
'<servicedescription xmlns="http://www.automart.com/servicedescription">
<description>
Change of oil and replacement of oil filter.
check of all filters, fluids and tire pressure
</description>
<parts>
<part>
<partname>oil quarts</partname>
<quantity>5</quantity>
<perpartprice>3.22</perpartprice>
</part>
<part>
<partname>Oil Filter</partname>
<quantity>1</quantity>
<perpartprice>3.15</perpartprice>
</part>
</parts>
<labor>
<workers>2</workers>
<hours>0.5</hours>
</labor>
</servicedescription>')
Select * from Person for xml raw('person'), elements, root('dataroot')
Select LastName, FirstName, LicenseNumber, VehicleMake, VehicleYear
From Person person
inner join Customer.vehicle vehicle
on person.personkey=vehicle.personkey
Where person.personkey=43
For xml auto, elements, root('Vehicles')
use CommunityAssist
alter table ServiceGrant
drop column GrantNotes
--if you wanted to save the existing grant notes
--you could do this before the previous statement
Insert into GrantTemp (grantKey, grantnotes)
Select GrantKey, GrantNotes from ServiceGrant
Create xml schema collection sc_ServiceNotesSchema
AS
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.communityassist.org/servicenotes" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="servicenotes">
<xs:complexType>
<xs:sequence>
<xs:element name="header">
<xs:complexType>
<xs:sequence>
<xs:element name="date" type="xs:string" />
<xs:element name="topic" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element maxOccurs="unbounded" name="note" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
alter table ServiceGrant
add ServiceNotes xml(sc_ServiceNotesSchema)
Update ServiceGrant
Set ServiceNotes=
'<?xml version="1.0" encoding="utf-8"?>
<servicenotes xmlns="http://www.communityassist.org/servicenotes">
<header>
<topic>Loan issues</topic>
<date>7/30/2013</date>
</header>
<note>
the client spent his last loan money on gambling, but he did
win more than we lent
</note>
<note>
I think he should pay us any profits
</note>
</servicenotes>'
Where GrantKey=2
Select * From ServiceGrant
use Automart
Select * from customer.AutoService
Here is the xslt file we used for AutoService Descriptions
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
exclude-result-prefixes="msxsl"
xmlns:a="http://www.automart.com/servicedescription"
xmlns="http://www.w3.org/1999/xhtml">
<!--xsl:output method="xml" indent="yes"/>-->
<xsl:template match="/">
<h2>Service Description</h2>
<p>
<xsl:value-of select="//a:servicedescription/a:description"/>
</p>
<h3>Parts</h3>
<ul>
<xsl:for-each select="//a:servicedescription/a:parts/a:part">
<li>
<xsl:value-of select="a:partname"/> ,
<xsl:value-of select="a:quantity"/>
<xsl:value-of select="a:perpartprice"/>
</li>
</xsl:for-each>
</ul>
</xsl:template>
</xsl:stylesheet>
Here is the default.aspx markup
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="AutoServiceID" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="AutoServiceID" HeaderText="AutoServiceID" InsertVisible="False" ReadOnly="True" SortExpression="AutoServiceID" />
<asp:BoundField DataField="ServiceName" HeaderText="ServiceName" SortExpression="ServiceName" />
<asp:BoundField DataField="ServicePrice" HeaderText="ServicePrice" SortExpression="ServicePrice" />
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AutomartConnectionString %>" SelectCommand="Select AutoServiceID, ServiceName, ServicePrice from Customer.AutoService"></asp:SqlDataSource>
</div>
<asp:Xml ID="Xml1" runat="server"></asp:Xml>
</form>
</body>
</html>
And finally here is the C# code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Xml;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection connect = new SqlConnection(
ConfigurationManager.ConnectionStrings["AutomartConnectionString"].ToString());
string sql = "Select ServiceDescription from Customer.AutoService where AutoServiceID=@ID";
SqlCommand cmd = new SqlCommand(sql, connect);
cmd.Parameters.AddWithValue("@ID", int.Parse(GridView1.SelectedRow.Cells[1].Text));
XmlReader xreader = null;
XmlDocument xdoc = new XmlDocument();
connect.Open();
xreader = cmd.ExecuteXmlReader();
xdoc.Load(xreader);
xreader.Close();
connect.Close();
Xml1.Document = xdoc;
Xml1.TransformSource = MapPath("ServiceDescription.xslt");
}
}
No comments:
Post a Comment