Solved

Sql Xml template with querystring

Posted on 2006-07-19
9
414 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where do I learn ES6? 3 81
JSON  parse help 8 52
I'm using XML(xslt) - How to fix the BOM issue 5 23
xml with php question 5 36
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

733 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