.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

Query a XML column using the nodes method

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server

Trying to learn xml, excuse my ignorance here....

I was able to write this query to shred the xml and get all the list_id's for just one row, I copied the xmldata and assigned it to a variable and it worked just fine.

declare @xmlData xml
set @xmlData = '<order tab_id="74" open="N" sorder_id="166" status="v2.0{1,2,3,4}">
  <list p_id="0" p_seq="0" list_id="10762" open="N" status="">
    <element elem_id="201041" elem_seq="14" click_id="1">
      <list p_id="10762" p_seq="14" list_id="10763" open="N" status="">
        <element elem_id="201054" elem_seq="284" click_id="2">
          <list p_id="10763" p_seq="284" list_id="10764" open="N" status="">
            <element elem_id="201376" elem_seq="669" click_id="3" />

View Complete Post

More Related Resource Links

preferred method to choose where to place if condition for query

I have a design problem I could use some help determining how to code this problem.   I have a table with 3 fields, catCode, startDate and endDate. I am having trouble deciding how to query my DB based on the following...   By default a gridview will return all records that match the catCode selected from a dropdown list.   If the user checks a check box, only records are returned where.... startDate <= today AND endDate >= today   How can I apply the If condition to compare when the check box is selected? I used the wizard to build the select statment for the gridview, but I can switch that to a SP if that makes life easier.   Thanks for any and all help and suggestions.  

Efficiency: new column in source query or derived column task?

Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Pipe SQL Query function into Variable with no column headings

Hello, I don't know if I'm in the right forum but could not find one for Powershell. I need to return 1 piece of data from a sql database to a variable. I don't want the column headings. Here's what I have so far:         Function GetMenuGroup ([int]$StoreID) { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=Servername;Database=DatabaseName;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "exec storedProcedure $StoreID" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet)|out-null $SqlConnection.Close() $DataSet.Tables[0] } $MG=Getdata 2 |out-string Here's the results I get now - How do I return just "DatathatIreallyWant" in a string variable? I don't want the column or underline. ColumnName ------------- DataThatIreallyWant   Thank you, MG2

Field method missing with LINQ query

H, I want to access the data from datatable object. When i trying to get the fieldslist usng "Field" method. But I could not find the method with my table object.  Eventhoguh i forcibly write a method and trying to access the fields, It is giving the below error for the first join in query. Rest of the lines are fine. What is the reason behind it. The errro: "Error 2 'LINQ2DB.SP_VS_OP' does not contain a definition for 'Field' and the best extension method overload 'System.Data.DataRowExtensions.Field<T>(System.Data.DataRow, string)' has some invalid arguments" What is the meaningof the error. Shall i do any other changes in the query line?                         var QueryChnl = from oSPOP in SPOP.AsEnumerable()                        join oDiscChannel in DiscChannel.AsEnumerable() on oSPOP.Field<int>("spid") equals oDiscChannel.Field<int>("SPID")                        join oChannel in Channel.AsEnumerable() on oDiscChannel.Field<in

How to filter managed meta data column using page query string?

I have two managed term sets as follows: Articles:Downloads Projects:Downloads I am trying to use a content query web part to filter a list based on the above manged meta data column. I want to filter out only "Articles:Downloads" Scenario 1: When I edit the web part and directly set the filter option with the managed meta data column to "Articles:Downloads" it works fine. When I export the web part, in the .webpart file, the "FilterValue1" property has a value "Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910". Scenario 2: I want the web part to get the filter information from query string [PageQueryString:pageType]. So I tried using these options: a) pageType=Downloads      This works but returns downloads under 'projects' as well. (i want downloads only under 'articles')  b) pageType=Articles:Downloads     Does not work and shows no records c) pageType = Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. (This value is the same as one found in 'FilterValue1' of the static web part). c) pageType = Articles:Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. d) pageType = 9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work.   What am I missing here? Can some one please help?

xml nodes query on DataContractSerializer string

Hi I need to get a table from this xml, I have some experience using nodes but am getting nowhere on this string.  It is produced from DataContractSerializer on a list with one of the columns a dictionary.  Any help will be much appreciated!  declare @xml xml = ' <ArrayOfArmorDeviceBase xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://server.armor.com/contract/data">   <ArmorDeviceBase xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="i1" i:type="ArmorSimpleDevice">     <Id>5000</Id>     <SiteId>0</SiteId>     <SourceSystem>Armor</SourceSystem>     <DataGroups xmlns:d3p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">       <d3p1:KeyValueOfstringDeviceDataGroupBaseRRFhSxY9>         <d3p1:Key>CommunicationStatus</d3p1:Key>         <d3p1:Value xmlns:d5p1="http://server.armor.com/contract/data/ControllerStatus" i:type="d5p1:CommunicationStatus">           <Ticks>0</Ticks>           <d5p1:Status>Disco

How to copy column headers along with the query result in SS00

HiIs it possible to copy the column headers along with the query result from SQL Server 2000 Query Analyser.Thanks in advance.fjz

How to enable delete column at Gridview when having specify query.

Hello:  I have trouble to add delete column to gridview when using specific query. I have PK set up at the table, I need add specific query to display data at gridview:   SELECT [PID], [GID], [TID], [SID], [NEWSID] FROM [TableOne] WHERE ([NAME] = ?)   After builder this query, I click advanced button. However, generate insert, update, and delete Statements is not enabled.   How to add delete column to this gridview?   Your help is highly appreciated.

SharePoint Dataview Insert New Mode - Defaulting a Required Column to a Query String Parameter Valu

I've got a SharePoint DataView with an insert button. there is a required lookup column I want to default to the value of Querystring Value during new mode.  Can I do this in the generated code below? I tried replacing @ApplicantId with @QSApplicantID.. and  tried string(@QSApplicantID) too.. no luck gave me an error: The data source control failed to execute the insert command. Could it be that QSApplicantID (which I've used in my DataView Datasource filter with no problem) is not available during the insert? I think I can do what I want in Javascript but was hoping I could instead right in the XSL markup. *my query string parameter**     ParameterBinding Name="QSApplicantID" Location="QueryString(ID)" DefaultValue="2222222"/ **the new form** SharePoint:FormField runat="server" id="ff4{$Pos}" ControlMode="New" FieldName="ApplicantId" __designer:bind="{ddwrt:DataBind('i',concat('ff4',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ApplicantId')}" / **the link** a href="javascript: {ddwrt:GenFireServerEvent('__cancel;dvt_1_form_insertmode={1}')}">Insert</a Thanks for any help or information!

Adding a new column in update query


Hi all,


Is it possible to add a new column in an update query? I am joing two tables  with [Encountered Month] from Table 1 and [Calendar Date] from table 2 and trying to add one column[Fiscal Month] into the first table where  dbo.table1.[Encountered Month]=dbo.table2.[Calendar Date]

Appreciate your help.

retrieving column names using sql query


hi All
 I have A database in access2007

I want retrieve column names with sql query in my project
I write below code:
"select column_name from information_schema.columns where table_name ='table 1' ORDER BY ORDINAL_POSITION"

but display error :
Could not find file 'D:\Projects\Tapco_ECM\Tapco_ECM\Tapco_ECM\bin\Debug\information_schema.mdb'.

plz help me

Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. Get information about collation of a column of a resultant select query using oledb



    I have executed a select query from a table using OLEDB (IID_ICommandText) . Now I want the information about collation(Case Sensitivity,Accent Sensitivity, Kana Sensetivity, Width Sensetivity, LCID) of all the eligible columns of a select query. Can anyone please help me with a way to retrieve all of them.



Two column Publishing page layout query


Hello I'm quite new to use sharepoint 2007 designer. I've set up a two column liquid publishing page layout template using two tables side by side. A total of 100% basically the left table is set to 70% and the right to 30%.

It works fine when used, however when no webparts or content are entered in the right column (table) the layout looks a bit strange since it the right column space is empty.

Is it possible for the left table to automatically expand to 100% if nothing is entered in the right table?

Thank you for any help that can be provided.

Column Alias Usage Everywhere in a Query Following Definition


In the following query, you cannot use the column aliases in WHERE and GROUP BY clauses, then suddenly like thunder from the blue, you can use it in the ORDER BY clause. It is counter-intuitive. It is counter-productive as well.

SELECT	[Year]=YEAR(OrderDate),
FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE YEAR(OrderDate) > 2002
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY [Year],[Month]
/* Year	Month	TotalSales
2003	1	1968647.184
2003	2	3226056.1486
2003	3	2297692.9898

I made a suggestion to enable column alias usage everywhere in a query. You can vote for this feature at Connect:


The error is: Method 'ProxyNamespace.ServiceNow_GetMetric.query' not found..


Hi guys -

I am working on a project where I need to call a WebService Method via the WebService Task in SSIS, and I am getting below error . The content of the WDSL file is also attached. Has anyone got this kind of error? I use BIDS with SQL Service 2008/Windows XP professional.

Thanks for your help


"[Web Service Task] Error: An error occurred with the following error message: Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException:
Could not execute the Web method. The error is: Method 'ProxyNamespace.ServiceNow_GetMetric.query' not found..

   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()". 


WDSL File content:

<?xml version="1.0" encoding="UTF-8"?>

<wsdl:definitions targetNamespace="http://www.service-now.com" xmlns:tns="http:

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