?
Solved

Sql Xml template with querystring

Posted on 2006-07-19
9
Medium Priority
?
417 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
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

 

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 2000 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

Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

777 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