November 17, 2011 at 2:55 pm
Good people:
Here is my situation. I have an ASP.NET Form with hundreds of input boxes that hold values for individual records. I have a mechanism where I use the table key to name each textbox. So, in the VB code, I can create an XML variable from this data. It is my intention to pass it to the database via a stored procedure that looks something like this:
create proc spLoadIt ( @xml XML)
as
begin
???
end
The question marks are where I am having issues. In fact, there are two of them, and I would really appreciate any help on either.
1) I would like to construct the XML in the following fashion:
<mytable keya="101" keyb="A" keyc="1" value="8.33">
<mytable keya="102" keyb="A" keyc="3" value="22.5">
<mytable keya="103" keyb="M" keyc="4" value="0.18">
...
In other words, have each element represent a single row and the attributes represent the columns -- a 3-part key and the value to store. Is this feasible?
2) In the upsert procedure, I would like to use the MERGE statement to make the insert/update procedure quick and painless. Unfortunately, I lack the right knowledge to both put something together and find what I need online. I get the basic syntax of the MERGE, but I don't know how to map the attributes in the elements to fields for use in the T-SQL code. Anyone know how to do this? My head is aching from all the google searching.
November 17, 2011 at 10:12 pm
james.jensen1350 (11/17/2011)
1) I would like to construct the XML in the following fashion:
<mytable keya="101" keyb="A" keyc="1" value="8.33"><mytable keya="102" keyb="A" keyc="3" value="22.5">
<mytable keya="103" keyb="M" keyc="4" value="0.18">
...
In other words, have each element represent a single row and the attributes represent the columns -- a 3-part key and the value to store. Is this feasible?
2) In the upsert procedure, I would like to use the MERGE statement to make the insert/update procedure quick and painless. Unfortunately, I lack the right knowledge to both put something together and find what I need online. I get the basic syntax of the MERGE, but I don't know how to map the attributes in the elements to fields for use in the T-SQL code. Anyone know how to do this? My head is aching from all the google searching.
--untested, xpath isn't my strong suite
select
T.C.value('./@keya', 'int') as KeyA,
T.C.value('./@keyb', 'char(1)') as Keyb,
T.C.value('./@keyc', 'int') as Keyc,
T.C.value('./@value', 'numeric(10,4)') as Decimal
into #tmpResults
from @xml.nodes('/mytable') as T(C)
MERGE into TargetTable using #tmpResults
....
November 18, 2011 at 6:22 am
--untested, xpath isn't my strong suite
select
T.C.value('./@keya', 'int') as KeyA,
T.C.value('./@keyb', 'char(1)') as Keyb,
T.C.value('./@keyc', 'int') as Keyc,
T.C.value('./@value', 'numeric(10,4)') as Decimal
into #tmpResults
from @xml.nodes('/mytable') as T(C)
MERGE into TargetTable using #tmpResults
....
Thanks! I had seen similar examples before, but I couldn't (easily) find something online that had a good explanation for the syntax. The main reason for that is because I packed attributes into the XML, whereas all the examples I found broke it all out into sub-elements.
Also, isn't the technique of creating a temp table on the fly like that in the update/insert statement called something? I can't find that either. ANY help on this would be greatly appreciated. Even a reference to a good book would be valuable.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply