• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Sql Xml template with querystring

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
vmaragos
Asked:
vmaragos
  • 5
  • 4
1 Solution
 
Anthony PerkinsCommented:
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
 
vmaragosAuthor Commented:
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
 
Anthony PerkinsCommented:
I am sorry, but I am not following you.  Post some sample data and the desired output.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
vmaragosAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
vmaragosAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
vmaragosAuthor Commented:
Can you help me , add this logic to my Sql query?
i think we are getting somewhere

thanks in advance
0
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now