Solved

Sql Xml template with querystring

Posted on 2006-07-19
9
409 Views
Last Modified: 2013-11-19
Hello experts.

I have a Sql XMl template and i want to run a search.

//MyCars.XML

<XML version="1.0" encoding="utf-8">
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:header>
      <sql:param name="id" />
      <sql:param name="brandid" />
      </sql:header>
      <sql:query>
            Select * From IndexCarsXML Where dealer = @id And Brand = @brandid FOR XML AUTO , ELEMENTS
      </sql:query>
</ROOT>
</XML>

I call this Xml file from my Domain.
How can i give results if eg Brandid = null.??

http://localhost/xml/myCars.xml?id=1000&BrandId=10 'returns all Mercedes cars.
http://localhost/xml/myCars.xml?id=1000 'returns nothing , but i want to return all (if brandid = null) then ?
0
Comment
Question by:vmaragos
  • 5
  • 4
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Change:
Select * From IndexCarsXML Where dealer = @id And Brand = @brandid FOR XML AUTO , ELEMENTS

To:
Select * From IndexCarsXML Where dealer = @id And (@brandid Is Null Or Brand = @brandid) FOR XML AUTO , ELEMENTS

Just remember that:
Null <> Null
0
 

Author Comment

by:vmaragos
Comment Utility
i ment ,

there is something to check if there are data in the var?

eg.

query = "select mpla mpla "
if @brandid <> "" then query = query & "where Brand = @Brandid"
....

because i have miltiple vars from querystring like (year,model,modeltype etc)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I am sorry, but I am not following you.  Post some sample data and the desired output.
0
 

Author Comment

by:vmaragos
Comment Utility
Lets say i have MyCars.XML
(the code presented to u above)

Query: MyCars.XML?id=1&brandid=10&model=evo&year1=2000&year2=2006&.... -->returns all cars with barnd 10 , model like 'evo' from year=2000 to 2006 etc

i may have multiple vars in querystring., or i just have 1 or 2

Query: MyCars.XML?id=1 --> returns all cars

Can i do all that in one XML file , and if yes how?

:-)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Something like this perhaps:

Select *
From IndexCarsXML
Where dealer = @id
          And Brand = @brandid
          And Model = @model
          And [Year] In (@Year1, @Year2, ...)
FOR XML AUTO , ELEMENTS
0
 

Author Comment

by:vmaragos
Comment Utility
I would like to give a hint.

In asp code i do the following.

id = request("id")
query = "Select * From IndexCarsXML Where Dealer=" & id & " "


if request("brandid") <> "" Then query = query & "and brand = " & request("brandid") & " "
if request("model") <> "" Then query = query & "and model like '%" & request("model") & "%' "
if request("year1") <> "" Then query = query & "and year >= " & request("year1") & " "
if request("year2") <> "" Then query = query & "and year <= " & request("year2") & " "
etc.

Can i do something like that in the XML file?
thanks



0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>Can i do something like that in the XML file?<<
No, Xml is just data,  that logic would have to go in the XSLT template file.  But you are better off putting that logic in your SQL query.
0
 

Author Comment

by:vmaragos
Comment Utility
Can you help me , add this logic to my Sql query?
i think we are getting somewhere

thanks in advance
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
Something like this:

Select *
From IndexCarsXML
Where dealer = @id
          And (@brandid = '' Or Brand = @brandid)
          And (@model = '' Or Model = @model)
          And (@Year1 = '' Or [Year] = @Year1)
          And (@Year2 = '' Or [Year] = @Year2)
FOR XML AUTO , ELEMENTS
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now