Passing variables in an XML String 1

[Cathryn]

We're trying to pass an XML string to a stored proc instead of multiple parameters. How do you take that XML string and just assign what is passed to different variables without creating a table to insert them or doing a plain
SELECT * FROM OpenXML... The only examples I can find return a rowset result. I want to use those variables throughout the stored proc to attain a recordset

 

[SQLSister]

If I undersatnd your need correctly, you want to send one string like "Virginia Beach, VA" and then use those data points to run a select statement that would translate to something like:
Select * from table1 where city = 'Virginia Beach' and state = 'VA'

Am I correct before I go down a wrong track?

 

[Cathryn]

Actually it's something like this...

SELECT @XML = '<SearchParameters Param1=&quot;123456&quot; Param2=&quot;1&quot; Param3=&quot;cabin&quot;/>'

The SQl Exec string is...
EXEC spStoredProc @Xml

The the stored proc would take the parameters in the XML string and assign them to variables to use in queries...

Create spStoredProc ( @XML varchar(4000) AS
Begin

DECLARE @VAR1 INT
DECLARE @VAR2 INT
DECLARE @VAR3 VARCHAR(20)

Select @Var1 = XML.Param1...


Is this even possible?

 

[sunila7]

Hi,


Does this give wht ur looking for .............



DECLARE @idoc int
DECLARE @doc varchar(1000)
declare @Param1 varchar(100)
declare @Param2 varchar(100)
declare @Param3 varchar(100)
SET @doc ='<SearchParameters Param1=&quot;123456&quot; Param2=&quot;1&quot; Param3=&quot;cabin&quot;/>'


EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT @Param1 = Param1,@Param2=Param2,@Param3=Param3
FROM OPENXML (@idoc, 'SearchParameters',1)
WITH (Param1 varchar(10),
Param2 varchar(20),
Param3 varchar(20) )
print @Param1
print @Param2
print @param3


Hope it helps

Sunil

 

[Cathryn]

Yes! Thank you so much! It worked perfectly!

 

[wepisan]

Hi,

Try the following:

declare @XmlHandle int

declare @MyTable TABLE
(
prequal_id bigint NOT NULL ,
loan_term_type varchar (15) NOT NULL ,
borrower_credit_type varchar (30) NOT NULL ,
loan_rate varchar(6) NOT NULL
)

EXEC sp_xml_preparedocument @XmlHandle output,
'<prequal_calculated_rate>
<record id=&quot;1&quot;>
<fields id =&quot;1&quot; loan_term_type=&quot;ARM 2/28&quot; borrower_credit_type=&quot;1 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
<fields id =&quot;2&quot; loan_term_type=&quot;ARM 3/27&quot; borrower_credit_type=&quot;1 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
<fields id =&quot;3&quot; loan_term_type=&quot;FIXED 20/20&quot; borrower_credit_type=&quot;1 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
<fields id =&quot;4&quot; loan_term_type=&quot;FIXED 30/30&quot; borrower_credit_type=&quot;1 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
<fields id =&quot;5&quot; loan_term_type=&quot;FIXED 15/15&quot; borrower_credit_type=&quot;1 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
<fields id =&quot;6&quot; loan_term_type=&quot;ARM 2/28&quot; borrower_credit_type=&quot;2 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
<fields id =&quot;7&quot; loan_term_type=&quot;ARM 3/27&quot; borrower_credit_type=&quot;2 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
<fields id =&quot;8&quot; loan_term_type=&quot;FIXED 20/20&quot; borrower_credit_type=&quot;2 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
<fields id =&quot;9&quot; loan_term_type=&quot;FIXED 30/30&quot; borrower_credit_type=&quot;2 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
<fields id =&quot;10&quot; loan_term_type=&quot;FIXED 15/15&quot; borrower_credit_type=&quot;2 Borrower Credit&quot; loan_rate=&quot;10.5&quot; />
</record>
</prequal_calculated_rate>'

insert into @MyTable
SELECT *
FROM OPENXML (@XmlHandle, '/prequal_calculated_rate/record/fields',1)
WITH (
prequal_id bigint '@id',
loan_term_type char (15) '@loan_term_type',
borrower_credit_type char (30) '@borrower_credit_type',
loan_rate varchar(6) '@loan_rate'
)

select * from @MyTable

EXEC sp_xml_removedocument @XmlHandle


Hope this helps.

Thanks,
Vaiyapuri Subramanian