Solved

Sql Xml template with querystring

Posted on 2006-07-19
9
416 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17140418
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
ID: 17140655
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
ID: 17141793
I am sorry, but I am not following you.  Post some sample data and the desired output.
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:vmaragos
ID: 17154616
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17156382
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
ID: 17159038
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
ID: 17159969
>>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
ID: 17160396
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
ID: 17161330
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
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:
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

696 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