Mapping variables from XML in t-sql

Question

Mapping variables from XML in t-sql

sweetdeal42

Old Hand

Points: 341
More actions
August 27, 2008 at 11:51 am

#192960

Hi,
I have a project where I need to convert an XML field to relational data and map the value to a variable that will be used in a stored procedure call. I have the XML conversion done and am storing the data in a two-column table variable where one column is a field name and the other is the corresponding value. I now need to assign that value to a variable in the stored procedure and call an insert statement.
My problem is mapping the value based on the field name to the correct variable. I can do different select statements with exact where clauses or a large if statement. CASE doesn't allow me to assign a variable based on the condition. There are about 350 values that have to be mapped for any one transaction.
Does anyone else have a clever idea? My other option was to create a .NET assembly that created a collection and select the data from there but that doesn't really eliminate the mapping problem.
Bright Ideas welcome!
thanks,
Maureen

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

This is a little too abstract to give a clear answer. Can you give some examples, please?

Even better, see this article for how to get better answers from the forums: http://www.sqlservercentral.com/articles/Best+Practices/61537/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sweetdeal42 Old Hand Points: 341 More actions · Answer 2

Thanks for the info. Let me see if I can make this more precise.

I have an XML file that I convert to a table that looks like this:

FieldName FieldValue

AcceptPaymentBeforeDelivery2

AcceptVisaMCDisc Yes

AcceptedByMerchant1 James Dean

There are two columns, "FieldName" and "FieldValue".

I now need to map the "FieldValue" value to a variable in a stored procedure based on the "FieldName" value.

For example:

If FieldName = 'AcceptPaymentBeforeDelivery' then @AccPay = FieldValue.

There are over 340 rows in the table so I need to do the mapping in the most efficient way.

I am wondering if there is some t-sql trick or tip that would make this easier. I am very familiar with PL/SQL but am fairly new to t-sql.

I hope this is clear enough.

Thanks!

Maureen

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

sweetdeal42 (8/27/2008)
There are over 340 rows in the table so I need to do the mapping in the most efficient way.
I am wondering if there is some t-sql trick or tip that would make this easier. I am very familiar with PL/SQL but am fairly new to t-sql.

Not directly into variables, no. You can either use a Select assignment or dynamic SQL. Dynamic SQL is probably more efficient, but then you would have to do everything else in the same dynamic SQL created batch also.

You can do the Select Assingment something like this:

Select

@AccPay = Max( Case

When FieldName='AcceptPaymentBeforeDelivery' Then FieldValue

Else Null End)

, @AccVisa = Max( Case

When FieldName='AcceptVisaMCDisc' Then FieldValue

Else Null End)

, @AccMerc1 = Max( Case

When FieldName='AcceptedByMerchant1' Then FieldValue

Else Null End)

, ...

From #tmpVariables

However, the real question here is: What are you really trying to do? Because there are usually a lot of other/better options besides throwing 350 columns into 350 variables. But it depends on what you are really trying to accomplish.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sweetdeal42 Old Hand Points: 341 More actions · Answer 4

You bring up a good point regarding 350 values to 350 variables.

The project is, a user can fill in a PDF and save it to the database. The PDF saves as an XML file. My part of the project is to take this XML file and get the data into a 3rd party RDBS. The 3rd party vendor has supplied a number of stored procedures that I can call to do the inserts. So I need to take the XML data and assign the values of the different elements to variables that can be passed to these stored procedures.

I am SURE there was an easier way to do this entire project but that is where we are at.

I'm going to check out the dynamic sql solution but I like your case statement.

Thanks!

Maureen