.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Asad Ali
Post New Web Links

Problem with an XML type column in this SQL Statement

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server
 

If I use an XML type variable (@xml) everything is fine with the following SQL:

SELECT p.number as Position,
  x.value('(../name)[1]','VARCHAR(200)') AS SectionName,
  x.value('(./label)[1]','VARCHAR(200)') AS Name
FROM master..spt_values p
CROSS APPLY @xml.nodes('/xxxx_settings/section/setting[position()=sql:column("number")]') n(x) where p.type = 'p'

However, when I try to store my XML data in a table that has an XML column the following query doesn't work:

SELECT p.number as Position,
  x.value('(../name)[1]','VARCHAR(200)') AS SectionName,
  x.value('(./label)[1]','VARCHAR(200)') AS Name
FROM master..spt_values p,
  dbo.XmlDataHolder a
CROSS APPLY a.XmlData.nodes('/xxxx_settings/section/setting[position()=sql:column("p.number")]') n(x) where p.type = 'p'

The error is "The column prefix 'p' does not match with a table name or alias name used in the query."

What am I missing here?




View Complete Post


More Related Resource Links

Need help with an Update statement, trying to update a 'bit' type column.

  

I have a function that checks for a username and if it finds the username it will allow the user to update the user, there are 3 main columns: Name,Age,isFemale.  I have the enduser supply the information and then I try to update the record but I get a sql error for "not a valid" column.

console app:

 Console.WriteLine("Please enter your fullname");
                        string spName = Console.ReadLine();
                        Console.WriteLine("Please enter you age, only use digits");
                         int spAge = int.Parse(Console.ReadLine());
                        Console.WriteLine("Are you a female?, 1 for Yes - 0 for No");
                        int spIsFemale = int.Parse(Console.ReadLine());
                        Person sp = new Person();
                        sp.name = spName;
                        sp.age = spAge;
                        
                        if (spIsFemale == 1)
                            sp.Gender = Gender.Female;
                        else
                            sp.Gender = Gender.Male;

                        Console.WriteLine(sp.savePerson());


I pass the spIsFemale by using an enum, here is the Person Class with function.

 public enum Gender { Female, Male };

class Pe

Bug: Does not change column type from nvarchar to ntext

  

Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.


To create a custom control and type while creating a column

  

HI All,

i am creating a column in the list while creating a column i will have an option for choice field on selecting choice i can see dropdown, radiobutton, Checkbox to select and  can enter list of values. and on clicking ok i will get a column with radiobutton. in newform.aspx and also in editform.aspx

so i need to create the similar functionality with my custom radio button, so on create column i need to show my custom choce on selecting this i need to show my custom radiobutton  and  can enter list of values then on selecting it then click ok to create a column, in edit form and new form i need to show a radio buttons with values.

Thanks in advance

 

 

 


How can I solve this problem?::Compiler Error Message: CS0433: The type 'CrystalDecisions.Web.Cryst

  

I upgraded my web application from visual studio 2003 to 2008. When I want to create a report in visual studio 2008 and receiving this error at runtime. I am able to compile but when runnning thats when I am getting this error.

How can I solve this problem? please help.

Compilation Error

Server Error in '/IRPSDEV' Application.

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0433: The type 'CrystalDecisions.Web.CrystalReportViewer' exists in both 'c:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Web\10.5.3700.0__692fbea5521e1304\CrystalDecisions.Web.dll' and 'c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\irpsdev\9e97e0a9\94a2ecd\assembly\dl3\151eb571\00df56e1_6064c201\CrystalDecisions.Web.DLL'

Source Error:

Line 161:        
Line 162:        [System.Diagnostics.DebuggerNonUserCodeAttribute()]
Line 163:        private global::CrystalDecisions.Web.CrystalR

Problem inserting into XML column

  
I have a C# application that inserts XML into Sql Server 2005.  When the message is more than 300 lines or so, it doesn't make it there.  The data shows in the application variables (stringWriter) but doesn't get inserted and that's confirmed looking in Profiler. The stored procedure is simply:          usp_WS_OrderEntry_InsertWSOrder @doc=@p1,@WSOrderId=@p2 output       INSERT INTO WS_OrderEntry_Order (OrderXML)       VALUES(@doc)  ..... C# .....             tableAdapter.usp_WS_OrderEntry_InsertWSOrder(stringWriter.ToString(), ref wsOrderId); .... In any cases, the variable "stringWriter" contains the data (as text). In profiler for a successfull call: declare @p1 xml set @p1=convert(xml,N'<OrderHeader><CustomerNumber>1234</CustomerNumber><Pr.....</PO></ShipToName> declare @p2 int set @p2=1077 exec dbo.usp_WS_OrderEntry_InsertWSOrder @doc=@p1,@WSOrderId=@p2 output select @p2 In unsuccessfull call: It shokes on the convert part and doesn't display any data (and doesn't insert as well).  I looked in LOGs, events and didn't find anything. Any help appreciated.  Thanks.

sove this type of problem what to do?

  
Hi ... Crystal Report Issue plz help ... I have developed an asp.net application with crystal report using visual studio 2005 n built in crystal report version 10 on win xp machine x86. its working fine in my PC. But the production server is 64bit OS windows server 2008 n IIS 7. My application is working fine , but am getting error in all crystal report page. They have installed Crystal report X64 in their server. Due to version conflict the report page not working. But i tried in 64bit Win 7 OS with juz crystal report x86 installed. Its working fine. I tld this to my service provider, but they are not ready to install crystal report X86 due to shared hosting. Plz help me , wat step i must take in order to resolve this issue ....

wpf charting column series data binding problem

  
I'm trying to build a chart using the columnSeries from the wpf toolkit and I appear to be having trouble with the data binding. Here is the xaml: <Grid>    <chartingToolkit:ColumnSeries Height="18" HorizontalAlignment="Left" Margin="188,169,0,0" Name="columnSeries1" VerticalAlignment="Top" Width="18" IndependentValueBinding="{Binding Path=Date}" DependentValueBinding="{Binding Path=Value}" /></Grid> In the code behind, I'm calling a stored procedure and putting the result in a datatable like this: string connString = ConfigurationManager.ConnectionStrings["string"].ConnectionString;            using (SqlConnection cn = new SqlConnection(connString))            {                DataTable dt = new DataTable("T1");                cn.Open();                SqlCommand cmd = new SqlCommand("T1_sp", cn);                cmd.CommandType = CommandType.StoredProcedure;                  SqlDataAdapter da = new SqlDataAdapter(cmd);                da.Fill(dt);                columnSeries1.ItemsSource = dt.DefaultView;                cn.Close();            } No error is being generated but I'm not getting a column chart either. If I take the same command and put it into a datagrid, it works fine. What do I need to do to get this into my column chart? If it helps,

Alternate lables are displayed along x axis in chart web part for column chart type

  
HI I am using Chart webpart of Sharepoint 2010. I am connecting this webpart to my custom list having two coulmn Title  (string) and Count (numeric) and using Column chart type i am displaying data in web part. Each column in chart represents one item in list and displyed along x axis, while y axis represents count. For more than 9 columns in column chart (i.e. more than 9 itmes in list) in chart web part only alternate lables are displyed along x axis. Any help in this will be appreciated.

SharePoint 2007 to Access 2007 - Issues grouping by "Person or Group" field type/column

  
We are on SharePoint 2007 and using Access 2007.  The SharePoint list contains a column called Assigned Person, column type: Person or Group, single selection, displaying only the name 1) When we Open with Access and Export a copy of the data, then create a report from that table, we do not have the option to group on Assigned Person.  2) When we Open with Access and Link to data on the SharePoint site, then create a report from that list, when we group by Assigned Person... The same person has 2 groups - there are is no other grouping or sorts on the list, and i can't locate a correlation to another field to show why they would have to collections for the same person The Person or Group column sorts "from smallest to largest" as though it is a number field. The list is a task/activity tracking system we created custom for our needs.  We want to pull a weekly report, grouped by Assigned Person, but cannot get access to work.  We do not want to resort to anything more complex, as it takes a much longer process to have reports pulled by Chrystal or one of those types of applications.  And we cannot change out the field for something like a Choice column as the list already has a very large amount of content.  Can anyone assist? Is there a setting hiding somewhere that is causing the problem? 

Basic data type problem with database insert

  
Hi,I'm trying to write some values into a database, but I think there's an issue with the data types.  Here's what I've got: public static string Hello(string pageId, string inOut) { string itemId = ""; string period = ""; var cookie = HttpContext.Current.Request.Cookies["visitorGUID"].Value; SqlConnection conn; SqlCommand comm; conn = new SqlConnection(GlobalSettings.DbDSN); comm = new SqlCommand("INSERT INTO myTable (memberGUID) VALUES (@memberGUID);", conn); comm.Parameters.Add("@memberGUID", System.Data.SqlDbType.Text); comm.Parameters["@memberGUID"].Value = cookie; try { conn.Open(); comm.ExecuteNonQuery(); return "Item added: cookie = " + cookie + " / pageId = " + pageId + " / inOut = " + inOut + " / pageId = " + pageId + " / itemId = " + itemId + " period = " + period; } catch { return "Item caught: cookie = " + cookie + " / pageId = " + pageId + " / inOut = " + inOut + " / pageId = " + pageId + " / itemId = " + itemId + " period = " + period; } finally { conn.Close(); } } At the moment I'm just trying to insert the value of the cookie into the database as a test.  It works fine when I

Problem while retrieving data from a lookup column from sharepoint linq.

  
Hi I am working with sharepoint linq concept. When I am giving a query with lookup column It's working fine.  But when i am trying to retrieve the data from a look up column to spgridview. I was unable to get the output. Here is the above code which i have worked.   string strTitle = string.Empty; string strStatus=string.Empty; var context = new LinqSampleDataContext(SPContext.Current.Web.Url); EntityList<SharePoint2010ConceptsItem> Concept = context.GetList<SharePoint2010ConceptsItem>("SharePoint 2010 Concepts"); var query = from cs in Concept where cs.Status.Title.ToString()!="Completed" select new { cs.Title, cs.Id, //cs.Status }; sgvConcepts.DataSource =query; sgvConcepts.DataBind();   Any help me plz how to retrieve the data from a lockup column.bvnprasad

Problem with Retrieving data from lookup column using SharePoint 2010 Linq Concept

  
Hi I am working with sharepoint linq concept. When I am giving a query with lookup column It's working fine.  But when i am trying to retrieve the data from a look up column to spgridview. I was unable to get the output. Here is the above code which i have worked.   string strTitle = string.Empty; string strStatus=string.Empty; var context = new LinqSampleDataContext(SPContext.Current.Web.Url); EntityList<SharePoint2010ConceptsItem> Concept = context.GetList<SharePoint2010ConceptsItem>("SharePoint 2010 Concepts"); var query = from cs in Concept where cs.Status.Title.ToString()!="Completed" select new { cs.Title, cs.Id, //cs.Status }; sgvConcepts.DataSource =query; sgvConcepts.DataBind();   Any help me plz how to retrieve the data from a lockup column.bvnprasad

using [content type] in calculated column

  
I want to get the content type in the calculated column. [content type] is available in Moss not able to use that in 2010. Any idea?

Problem with Dimension of type Account

  
Hi, I have a simple fact table where I have a dimension time and a dimension of type acount. The fact table looks like this FinanceKey AccountKey ProfitCenterKey DateKey DateAlternateKey ScenarioKey Amount 1 11 5 7308 2010-01-01 1 131 2 11 3 7308 2010-01-01 1 62115 3 11 0 7308 2010-01-01 1 27626 4 11 6 7308 2010-01-01 1 12 5 11 2 7308 2010-01-01 1 74316 6 11 7 7308 2010-01-01 1 14 7 11 5 7339 2010-02-01 1 147 The account dimension looks like this AccountKey AccountAlternateKey ParentAccountKey AccountName_en AccountName_de AccountType Operator CustomMembers ValueType CustomMemberOptions 1 1 NULL Statistical Accounts Statistische Kennzahlen Statistical ~ NULL Units NULL 11 11 1 Customer Count Anz. Kunden Balances ~ NULL Units NULL If I browse my cube I get no data for the measure amount. When I remove hte parent Account "Statistical Accounts" and ste the parent of "Customer Count" to NULL I will get the correct Customer count. Any idea?

Insert Image column in External content type

  
Hi,   I've created an external content type. It pulls in data from SQL Server Database. I need to have the first column as an image based on the value of one of the columns. Could anyone please suggest how to add a column so represent an image in external list.   Regards, Zia
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend