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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Querying An XML Field In SQL 2008

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

Working in SQL 2008

Sample Data:

<Listing xmlns="http://xxx.org/xsd/Syndication/2008-03">
    <commons:preference-order xmlns:commons="http://xxx.org/xsd/RETSCommons/2007-08">1</commons:preference-order>
    <commons:address-preference-order xmlns:commons="http://xxx.org/xsd/RETSCommons/2007-08">1</commons:address-preference-order>
    <commons:FullStreetAddress xmlns:commons="http://xxx.org/xsd/RETSCommons/2007-08">16 Reliance Ct.</commons:FullStreetAddress>
    <commons:City xmlns:commons="http://xxx.org/xsd/RETSCommons/2007-088">Telford</commons:City>
    <commons:StateOrProvince xmlns:commons="http://xxx.org/xsd/RETSCommons/2007-08">P

View Complete Post

More Related Resource Links

AdomdDataAdapter.Fill returns memory error when querying an SSAS Cube(2008)

For certain SQL's I will get the following error when calling the Fill method on an AdomdDataAdapter object. "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."   Snippet of Code: DataSet ds1 = new DataSet(); AdomdCommand acmd1 = cn1.CreateCommand(); acmd1.CommandText = "SELECT ..."; AdomdDataAdapter ad1 = new AdomdDataAdapter(acmd1); ad1.Fill(ds1);   Now when we run this query in Ms Sql Studio we don't get any errors but the query returns 1 row with over 40,000 columns. I'm guessing the # of columns might be an issue but not sure.   When doing a Google search it appears that this problem was found in the 2005 and fixed in that version. Have any of you come across this problem?  

SSRS 2008 R2 Dataset Field Value Property Data Type not numeric

We're building SSRS 2008 R2 reports using SSAS as data sources.  There are several occaissions where the data types of certain dataset field values that represent cube measures are not numeric.  As such, when these fields are used in an aggregate function they throw an error.  Is there a way to remedy this in the dataset? Thanks for your help.

Concatenating several rows data into one field Visual Studio 2008


Below is my a example of my table and data.  I am using Visual Studio 2008.  I need to concatenate field customer_report with like field ‘number’.  I added a new SQLDataSource and put the query below into it.


Select Main.number,

       Left(Main.CustomerNotes,Len(Main.CustomerNotes)-1) As "CustomerNotes"

From(Select distinct PS2.number, 

           (Select PS1.customer_report + ',' AS [text()]

Is it possible to save "Collections" to a single varbinary(MAX) field/column in a SQL Server 2008 da


I was thinking about the fastest way to retrieve a "set" of objects related to a specific user. For instance, if I was to create a website similar to Flickr where people upload lots and lots of photos(jpegs) what would be the fastest way to be able to retrieve those photos from the SQL Server database. Scenario 1: You store metadata about each file they upload to the website as a single row in a database table. Then when you want to retrieve a specific users uploaded files you simply scan the database file for every row matching the UserID and store them in memory.

SELECT PictureId, UserId, PathToFile, Caption, (etc.)

FROM Photos

WHERE UserId=@UserId

This, of course, gets all the information about all the photos a single user has uploaded. My concern with this approach is when the user activity goes from a few thousand to being in excess of a million.

This approach seems fine for a small website but does this method work for a million plus user website like Flicker or MySpace? When you have a million users and millions more photos does this approach still perform at acceptable speeds?

My idea was to actually attempt to create a collection in memory of "ALL" the PictureIds' for a single user and store them in-memory, in a collection and then save this collection as a single VARBINARY(MAX) field in the SQL

MSSQL 2008 xml field CDATA content


How is it possible to store xml (untyped) content with CDATA tags ito a xml field without automatic removing of the CDATA tag?
At the moment I need a second field varchar(max) to store the real xml contet and I use the xml field for xQuery searches.

But there is a problem is characters like '<' in the node text will be replaced with escaped characters! - for other processes I need the real stored content!
To use a xml schema is not possible because the xml structure can be variable - I need untyped xml.

Any sugestions welcome

Thanks for your response

[sql 2008] Querying XML with XSD-definition gives no results



Currentyly i am trying to parse some XML documents as part of a query.
in it, i need to convert to an XML (source is Ntext and i cannot just alter the source-database).
So this should be no issue with the XML handeling of 2008 but something odd is going on, i have an XSD definition in the file but i have no XSD-schema's in the database. SO i would expect its a untyped XML even if the XML has an XSD-reference attached, but if i run the query i get 0 results.
If i remove the XSD-tag, the query gives results. To demonstrate this i have create the query attached below that isolates my issue. Has anybody seen this before and know a way around it?

With kind regards,

Sebastian van den Putten

DECLARE @Document varchar(max)
DECLARE @DocumentWithXSD varchar(max)

set @Document = '<CreateCustomerInput >

set @DocumentWithXSD = '<CreateCustomerInput

Reporting Services 2008: Columns get shifted on Tablix after hiding a field.



I have a report implemented on SSRS 2005 that for the purpose of hiding the toggle sign (+/-) when there is no detail data has an expression on the Hidden property of the text box.

Now when migrating to 2008, the table has been automatically upgraded to a tablix with the undesired behavior that, when a field is hidden on a row, the remaining fields are shifted to the left to occupy the space of the hidden field.

This happens only when the report is rendered on the web. On Visual Studio it works as in 2005.

Is there a way to avoid this kind of shifting? Why will I want to render a field on a space designated to the field on the left that it may not even fit?




Querying a SharePoint List: Creating a Report Parameter based on a SharePoint List field

I have been struggling with this problem over a week now and am at my wits end.   I have created a RS(2005) report and am currently querying data from a SharePoint List.  If anybody is interested in seeing how I set it up I follow this simply walk through on Code Project.  http://www.codeproject.com/KB/reporting-services/ReportFromSharePoint.aspx.  

I need to create a custom report parameter and reference a field that I have created in my SharePoint list.   When I run the report I would like to 'Filter' the result set with the value selected in the report parameter field.  I have looked anywhere and everywhere and don't know if I am over complicating this problem but I have yet to find a solution. 

Please and Thank you
Software Engineer

Querying a field that contains CR/LF returns no results


Using SqlCeCommand without parameter binding, I'm running a query against a field that contains a carriage return and line feed.  On the .NET side of things, the query I'm executing is passing "\r\n".  I get no results back.  Shouldn't this work?

Thank you,


Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Sql Server 2008 Tutorials

This Link Provides total Sql Server 2008 Tutorials

Seven reasons to care about SQL Server 2008 R2

Microsoft begins to fully realize its vision of SQL Server as an information platform and not "just" a database. Hence the main theme for this release -- at least according to Microsoft -- is self-service BI. The PowerPivot plug-ins for Excel 2010 and SharePoint 2010 are easily going to make the biggest splash of all the new features, not least because they're the most complete. But then, SQL Server 2008 R2 isn't strictly necessary for PowerPivot for Excel, which works with plenty of other data sources.

calculation, field and map traverse adjustment, and coordinate transformation

Free Pocket PC land surveying software -- COGO calculation, field and map traverse adjustment, and coordinate transformation -- for students and professionals.

Building Re-Usable ASP.NET User Control and Page Libraries with VS 2008


I am having problems with a user control. I want my solution to have a separate project for holding my user controls.

The main project can get a reference to this user control project and can import this user control dll into my main project. 

Thanks in advance,

Dheeraj Mittal


Dropdown Event (Template Field)



I have grid view control that includes a dropdown control in a template field.

I wish to execute some code when the value is changed in the dropdown list.  Can't figure out how to capture this event though?

Any suggestions?

How to take value of bound field of grid in a variable or in a textbox.


How to take value of bound field of grid in a variable or in a textbox.

 I m using this code in .aspx page for grid.

<asp:GridView ID="Grd_Mprocess" runat="server" AutoGenerateColumns="False"

                      Width="100%" AllowPaging="True"


                      AllowSorting="True" PageSize="5"  >

                 <RowStyle CssClass="odd" />


<asp:BoundField HeaderText="DOC_TYPE_PK" DataField="DOC_TYPE_PK"  Visible="true" >


  <asp:TemplateField HeaderText="DOC TYPE ID" Visible="true">  &nbs

Visual Web Developer 2008


I'm getting th error "unable to connect to the asp.net development server 2008"

It's been working fine for months and all the sudden.I'm fairly new at this so if you have a fix I will need details.



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