I have the following sql query and want to get the values of only the CURCVY node values from 1-4 so I can insert each node to a table. but all it inserts are the node names
DECLARE
@MYARRAY table (TEMPCOL nvarchar(50)),
@tvar char(100),
@cnt INT = 1,
@cnt_total int =5,
@xmldata xml='<NewDataSet>
<ROWS>
<POLIAGIN>PRE-AUTHORIZED</POLIAGIN>
<CURCVY01>0.00</CURCVY01>
<CURCVY02>253.00</CURCVY02>
<CURCVY03>1523.50</CURCVY03>
<CURCVY04>2815.50</CURCVY04>
<CURCVY05>4129.00</CURCVY05>
</ROWS>
</NewDataSet>'
WHILE @cnt < @cnt_total
BEGIN
set @tvar = 'CURCVY0' cast(@cnt as char(5))
EXEC sp_insert_to_my_table @xmldata.query('NewDataSet/ROWS/*').value('(sql:variable("@tvar"))[1]','nvarchar(max)')
SET @cnt = @cnt 1;
END;
this is what the sp does
--sp_insert_to_my_table
declare
@amount as vachar(max)
insert into myTable (column1)
values (@amount)
UPDATE
I've tried the following method and now I get null values
EXEC sp_insert_to_my_table @xmldata.value('(/NewDataSet/ROWS/*[local-name() = sql:variable("@tvar")])[1]','nvarchar(max)')
CodePudding user response:
Guessing what you need.
SQL
DECLARE @maxValue CHAR(2) = '04'
, @xmldata XML =
N'<NewDataSet>
<ROWS>
<CURCVY01>0.00</CURCVY01>
<CURCVY02>253.00</CURCVY02>
<CURCVY03>1523.50</CURCVY03>
<CURCVY04>2815.50</CURCVY04>
<CURCVY05>4129.00</CURCVY05>
</ROWS>
</NewDataSet>';
SELECT c.value('text()[1]', 'decimal(10,2)') AS Result
FROM @xmldata.nodes('/NewDataSet/ROWS/*[substring(local-name(.),7,2) le sql:variable("@maxValue")]') AS t(c)
Output
---------
| Result |
---------
| 0.00 |
| 253.00 |
| 1523.50 |
| 2815.50 |
---------
CodePudding user response:
Looks like this is the code you need:
DECLARE
@maxValue int = 4,
@xmldata xml='
<NewDataSet>
<ROWS>
<POLIAGIN>PRE-AUTHORIZED</POLIAGIN>
<CURCVY01>0.00</CURCVY01>
<CURCVY02>253.00</CURCVY02>
<CURCVY03>1523.50</CURCVY03>
<CURCVY04>2815.50</CURCVY04>
<CURCVY05>4129.00</CURCVY05>
</ROWS>
</NewDataSet>';
INSERT myTable (column1)
SELECT x.CURCVY.value('text()[1]','decimal(18,5)')
FROM @xmldata.nodes('NewDataSet/ROWS/*[substring(local-name(), 1, 6) = "CURCVY"][position() <= 4]') x(CURCVY);
Note how the position()
predicate is separate, therefore it only begins counting after the substring()
has filtered.