MSSQL : The argument x of the xml data type method "y" must be a string literal.
While playing around with the xml data type in SQL I noticed an interesting issue (maybe it will save someone some time), observe the following snippet:
DECLARE @cities AS XML SET @cities = '<cities> <city cityID="1" value="Berlyn" countryID="1" /> <city cityID="2" value="Hamburg" countryID="1" /> <city cityID="3" value="Frankfurt" countryID="1" /> <city cityID="4" value="Rotterdam" countryID="2" /> <city cityID="5" value="Amsterdam" countryID="2" /> </cities>' SELECT item.value('@cityID', 'INT') cityID, item.value('@value', 'VARCHAR(255)') value FROM @cities.nodes('//city[@countryID = 1]') t (item)
Notice the XPath "nodes('//city[@countryID = 1]')", this statement will return all the cities listed for Deutschland in the XML string.
If we want to make this more generic we can always simply pass a value to a where clause on the query like this:
SELECT item.value('@cityID', 'INT') cityID, item.value('@value', 'VARCHAR(255)') value FROM @cities.nodes('//city') t (item) WHERE item.value('@countryID', 'INT') = @countryID
But what if we want(perhaps need) to pass a value to a XPath statement? At first we might consider doing the following:
SELECT item.value('@cityID', 'INT') cityID, item.value('@value', 'VARCHAR(255)') value FROM @cities.nodes('//city[@countryID = ' + @countryID + ']') t (item)
This will however generate an error "The argument 1 of the xml data type method "nodes" must be a string literal", for this purpose e.g. interaction between relational and xml data, one can use expressions like sql:variable & sql:column.
DECLARE @countryID AS INT SET @countryID = 1 SELECT item.value('@cityID', 'INT') cityID, item.value('@value', 'VARCHAR(255)') value FROM @cities.nodes('//city[@countryID = sql:variable("@countryID")]') t (item)
sql:variable being a variable available within scope of our stored procedure, sql:column a field available within our query.
Some additional reading.
Posted by - Christoff Truter
Date - 2010-05-18 22:28:09
Comments - 1
Date - 2010-05-18 22:28:09
Comments - 1
ASP.net(C#): Custom Templated Control
Source Code
What is a templated control exactly?
Its basically a control that provides us with greater control over the markup rendered by our controls, an
example of a templated control in .net is the ListView control.
<asp:ListView runat="server" ID="lv" ItemPlaceholderID="ph"> <LayoutTemplate> <table> <tr> <td> ID </td> <td> Title </td> </tr> </table> </LayoutTemplate> <ItemTemplate> <tr> <td> <%# Eval("ID") %> </td> <td> <%# Eval("Title") %> </td> </tr> </ItemTemplate> </asp:ListView>
In the preceding snippet LayoutTemplate & ItemTemplate represents templated parts of the control.
In order to add this functionality to our own controls, we make use of the ITemplate interface like in the following basic snippet:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Linq; using System.Text; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace CustomControls { [ParseChildren(ChildrenAsProperties = true)] [Designer(typeof(CustomTemplateDesigner))] public class CustomTemplateControl : CompositeControl, INamingContainer { [Browsable(false)] [PersistenceMode(PersistenceMode.InnerProperty)] [TemplateContainer(typeof(CustomTemplateControl))] public ITemplate FirstTemplate { get; set; } [Browsable(false)] [PersistenceMode(PersistenceMode.InnerProperty)] [TemplateContainer(typeof(CustomTemplateControl))] public ITemplate SecondTemplate { get; set; } protected override void CreateChildControls() { if (FirstTemplate != null) { FirstTemplate.InstantiateIn(this); } if (SecondTemplate != null) { SecondTemplate.InstantiateIn(this); } base.CreateChildControls(); } } }
Notice the properties FirstTemplate & SecondTemplate, these properties expose our templates. Remember to set the PersistenceMode Attribute on these properties in order to persist the contents of these controls (a few MSDN examples excluded it).
Once we drop our control onto a page, these templates become available like this:
<cc:CustomTemplateControl ID="CustomTemplateControl1" runat="server"> <SecondTemplate> 2 </SecondTemplate> <FirstTemplate> 1 </FirstTemplate> </cc:CustomTemplateControl>
We can also add some design time support to our templated control like demonstrated in the following crude snippet:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.ComponentModel.Design; using System.Web.UI.Design; using System.Web.UI; using System.Web.UI.Design.WebControls; using System.ComponentModel; namespace CustomControls { public class CustomTemplateDesigner : CompositeControlDesigner { private CustomTemplateControl _Control; public override void Initialize(IComponent component) { base.Initialize(component); _Control = (CustomTemplateControl)component; } private EditableDesignerRegion GetEditableRegion(ITemplate template, string title, string index, StringBuilder sb) { IDesignerHost host = (IDesignerHost)GetService(typeof(IDesignerHost)); sb.Append(String.Format(@"<tr> <td style=""font-family: Arial;background-color:#CCC""> {0} </td> </tr> <tr> <td {1}='{2}'> {3} </td> </tr>", title, DesignerRegion.DesignerRegionAttributeName, index, ControlPersister.PersistTemplate(template, host))); return new EditableDesignerRegion(this, String.Concat(DesignerRegion.DesignerRegionAttributeName, index), false); } public override string GetDesignTimeHtml(DesignerRegionCollection regions) { StringBuilder sb = new StringBuilder(); sb.Append(@"<table style=""border:1px solid #CCC"">"); EditableDesignerRegion region = GetEditableRegion(_Control.FirstTemplate, "First", "0", sb); EditableDesignerRegion region2 = GetEditableRegion(_Control.SecondTemplate, "Second", "1", sb); sb.Append("</table>"); regions.Add(region); regions.Add(region2); return sb.ToString(); } public override string GetEditableDesignerRegionContent(EditableDesignerRegion region) { IDesignerHost host = (IDesignerHost)Component.Site.GetService(typeof(IDesignerHost)); if (host != null) { ITemplate template = (region.Name == String.Concat(DesignerRegion.DesignerRegionAttributeName, "0")) ? _Control.FirstTemplate : _Control.SecondTemplate; if (template != null) return ControlPersister.PersistTemplate(template, host); } return String.Empty; } public override void SetEditableDesignerRegionContent(EditableDesignerRegion region, string content) { if (content == null) return; IDesignerHost host = (IDesignerHost)Component.Site.GetService(typeof(IDesignerHost)); if (host != null) { ITemplate template = ControlParser.ParseTemplate(host, content); if (region.Name == String.Concat(DesignerRegion.DesignerRegionAttributeName, "0")) _Control.FirstTemplate = template; else _Control.SecondTemplate = template; } } } }
In a future post we will have a look at how to databind templated controls.
Posted by - Christoff Truter
Date - 2010-05-14 23:57:58
Comments - 0
Date - 2010-05-14 23:57:58
Comments - 0
First 1 2 3 4 5 6 7 8 9 10 Last / 42 Pages (83 Entries)