How to retrive multiple XML node values from SQL2005 XML datatype? (Sample DB Schema Given)

Hi all, 

 I'm having two tables in SQL 2005 as below and column SearchCriteriaXML in SearchCriteria table is in XML datatype

Table : SearchCriteria    
SearchCriteriaId SearchName SearchCriteriaXML
1 Company Name

<Criterias>

<Criteria><ColumnName>Company.CompanyName</ColumnName><Condition>LIKE</Condition></Criteria>
<Criteria>><ColumnName>Company.CountryId</ColumnName><Condition>=</Condition></Criteria></Criterias>
2 Project Name <XML><FieldName> Company.CompanyName</FieldName></XML>
 
Table: SearchColumn    
SearchColumnId SearchCriteriaId SearchColumnQuery
1 1 SELECT DEALID,DEALTYPE,MLROLE
FROM TRANSACTIONMASTER...
[Dynamic WHERE Query]
2 2 SELECT COMPANYNAME
FROM COMPANY ...
[Dynamic WHERE Query]
 

I need to get the select query from SearchColumnQuery in SearchColumn table and dynamically generate the where query from SearchCriteria table here as below (input parameter for query ll be thru SP input parameters)

basically, I'm generating dynamic select query from the two tables.

SELECT Comapnyid,countryid,cityid FROM company WHERE Company.CompanyName LIKE ‘%XYZ%’ || Company.CountryId=1

Please help me out from this. Expecting Quick reply as early as possible.

Thanks

-bala

 

Please remember to click “Mark as Answer” on the post that helps you

'work smarter not harder'
0
balavanchi
2/6/2008 8:25:16 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

0 Replies
799 Views

Similar Articles

[PageSpeed] 12
Get it on Google Play
Get it on Apple App Store

Reply:

Similar Artilces:

Getting standard SQL datatypes from XML, i.e. XML->SQL
Hi all, I'm wanting to get standard SQL data types from an XML document in ASE. Looking at the documentation in 'XML Services in Adaptive Server Enterprise', it all seems to be concerned with transforming SQL into XML, or manipluating XML. e.g. how do you get a query like :- select filename, xmlextract("//book/title" , content) from xmlsfsTab to return a 'SQL' result set so the title column does NOT containt XML fragments, i.e. it looks like:- filename title ---------------- -------------------------------- bookstore.1.xml ...

Adding a XML schema to XML schema collection
I used SSEUtil to add a schema to my database but I am having problems.  Used these steps:SSEUtil -c> USE "c:\Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database.  Any ideas on what I am doing wrong or why this might be happ...

XML in, XML out
Hi All (and a happy holiday to those that will get a break), I am trying to read in an XML file of addresses. I need to remove all the address data from the file where code =~ /^000/ (there are none in the example data below). I need to reproduce that data 'as is', so I need to honour the tag structure, although the order of the tags doesn't need to be honoured. I have been trying to use XML::Simple and I had a go with XML::Smart but I haven't been able to get the results I want. My best effort is below (with XML::Simple). There are a couple of differences ...

XMl to XML
Hello,   I retrieved an XML from the dataset which retrieves data from the table in the database. I need to present the data  in a different structure. Is XSLT the way or are there any other options. please let me know. Thanks!! Yes XSLT is a good choice to convert xml file to other forms. You can take a look at XSLT Tutorial.Sincerely,Young Fang...

Xml schema to Xml file
Is it possible to convert xml schema to xml file.How can i do read&writexmlschema using a dataset with xml schema. I converted a xml file to xml schema in a web appliction project.Then what should be location of shema that i need to give for a dataset I mean dataset.readxmlscheme(server.mappath(??))   Please let me know I am new to this field     dataset.ReadXml(Server.MapPath("xml\ProductList.xml")) will read the xml as well as the schema of thexml file. no need to convert it into schema file while using dataset as it will automatically define the ...

XML & XML Schema
 Hi Can I use XML natively instead of a XML Schema.The schema is very simple and I would like to see if it's possible to use just XML. <!--W3C Schema generated by XMLSpy v2007 (http://www.altova.com)--> <!--Please add namespace attributes, a targetNamespace attribute and import elements according to your requirements--> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> <xs:import namespace="http://www.w3.org/XML/1998/namespace"/> <xs:element name="issue"> <...

Xml Schema from xml file
How should i create xml schema from xml file?  Do you mean an XSD?  That's easy--open the XML file in Visual Studio.  There will be a new XML menu button, and one option is to create an XSD.  If you have a lot of files, MS has a little tool at http://msdn.microsoft.com/en-us/vbasic/bb840042.aspx.If my post is your answer, please mark it as the answer. It will bring good karma.Crystal AllianceMy BlogFlorist Blogs  Thanks alot!!  It worked wonderfully...

write to a xml with a xml schema
Hi, I have a xml schema, and a xml that validate this schema.I want to append new nodes to this xml with code (c#)which is the best form to do this?thanks...

Retriving XML data from a SQL Server 2005 XML column and display in table format.
Hi guys, I would like to (in code behind C#) retrive XML data that is stored in a SQL Server recordset and process this data and display this in a userfriendly format let say within a Wizard control.  My idea is someting like this, <Root> <AirSeg>    <SegNum>1</SegNum>    <Dt>20061230</Dt>    <AirV>EK</AirV>    <StartAirp>SYD</StartAirp>    <EndAirp>DXB</EndAirp> </AirSeg> <AirSeg>    <SegNum&g...

how to read XML node.Name and node.Value
Hi experts! I am trying to write a samll test script on the following xml file. I have a node <Type> which has different values. How can I read <Type> node name and its values to perform different operations based on the values? I need your help. Thank you in advance <?xml version="1.0" ?> <List> <Identity>     <Type>500</Type>    <Number>11111</Number>     <Name> Sara</Name> </Identity><Order>   <OrderID>2000</OrderID>  &...

Replace a node from 1 xml file with a node from another xml file
Hi,  suppose i have these 2 xml files: File 1: <resultset morerecords="0" paging-cookie="1">accountid last="{D14D44AD-36D0-DC11-AA32-0003FF33509E}" first="{D14D44AD-36D0-DC11-AA32-0003FF33509E}" /></cookie>"> <result> ... <donotpostalmail name="Allow">0</donotpostalmail> <name>Adventure Works</name> <primarycontactid name="Abhijit Thakur" dsc="0" yomi="Abhijit Thakur">{0820590A-37D0-DC11-AA32-0003FF33509E}</primarycontactid>...

Any good example for write, read a specific node value from a xml file using C#.net
  Is there any good example for write, read a specific node value from a xml file using C#.netRegards Anu Palavila > using System.Xml; > using System.Xml.XPath; > Insertion: >         XmlDocument xmlDoc = new XmlDocument(); >         xmlDoc.Load(MapPath("~/App_Data/Users.xml")); >         XmlDocumentFragment docFrag = xmlDoc.CreateDocumentFragment(); >         string id = txt_ins_uid.Text; >         string name = txt_ins_uname.Text; >    ...

XML Schema that allows XML Fragments
I'm writing a little tool that would allow clients to create customizable profiles on my website. The profiles use a special XML language that will be parsed and transformed into an HTML document. A sample XML looks like this:   <websites> <header> <div class="company_websites"> </header> <content> <p> #SITE_NAME </p> </content> <footer> </div> </footer> </websites>  Most elements in the XML allow user to defi...

create an xml schema from an xml file
Hello, I need to write an application that uses a xml file that i create and make a xml schema based on that file. I have a seperate company that is sending us the xml file and i want to get the info from the file and put it into a database. However, they need the my schema. I am reading up on how to use the DataSet.Writexml() and DataSet.WriteXmlSchema(). I know that there is some way of getting the data from the xml file using a DataSet, but i dont understand how. Has anyone done something similar to this that could help me at least get started? Thanks Dev Djinnx-Dev DjinnxWeb Develop...

Web resources about - How to retrive multiple XML node values from SQL2005 XML datatype? (Sample DB Schema Given) - asp.net.sql-datasource

Datatype generic programming in Scala
... level fixpoints and how it can be used to define generic abstractions like catamorphism and anamorphism . These are used extensively in datatype ...

Sabbatical week day 3: Raining datatypes
... 2: I fail at Octave (swizec.com) Learning me a Haskell (swizec.com) Non-Sabbatical (ringingtrue.net) How to insert a file to an Image datatype ...

Integer - Wikipedia, the free encyclopedia
) are formed by the natural numbers (including 0) ( 0 , 1 , 2 , 3 , ...) together with the negatives of the non-zero natural numbers ( −1 , −2, ...

CHARITY - Home Page
Charity is a categorical programming language currently beingdeveloped by The Charity Development Group in The Department of Computer Science ...

Adobe Director 11.5: Upgrade details
Accessibility Adobe Products Acrobat Creative Cloud Creative Suite Digital Marketing Suite Digital Publishing Suite Elements Photoshop Touch ...

Anzo Express - Cambridge Semantics
Semantic collaboration platform providing secure data integration, collaboration, and reporting in Microsoft Excel and on the Web.

Betting the Company on Windows 8
... and create something far less expensive than a PC which is powerful enough for Web browsing. This new platform would optimize for the datatypes ...

Q&A: SQL Date format for credit card - TechRepublic
Hi There, I am very new to MySQL and am creating a database for online payments etc. What datatype do I need to use for the credit card expiry ...

Data types - DBMS 2 : DataBase Management System Services
Analysis of data management technology optimized for specific datatypes, such as text, geospatial, object, RDF, or XML. Related subjects include: ...

Functional Programming with Python – Part 1
... instances wherein any modifications to the string result in a new String instance being created. Imagine the same happening to all the datatypes ...

Resources last updated: 12/25/2015 12:46:43 PM