Georg Machacek wrote:
It does not loop thrue the XML.
When you use:
SELECT xmlTable.ID.value('int[1]','int') from @LocXml.nodes('/ArrayOfInt') xmlTable(ID)
it can't loop thru the xml nodes, because of:
'int[1]'
, which means get the first record from
ArrayOfInt
.
To get all
int
data from
ArrayOfInt
, use code:
DECLARE @xmlDoc AS XML
SET @xmlDoc ='N<arrayofint xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<int>1013</int>
<int>1015</int>
<int>1016</int>
<int>1017</int>
</arrayofint>'
DECLARE @tmp TABLE (ColumnHeader NVARCHAR(50), ColumnVal(30))
INSERT INTO @tmp (ColumnHeader, ColumnVal)
SELECT bar.value('local-name(.)','nvarchar(50)') as ColumnHeader,
bar.value('(./.)','varchar(30)') as ColumnVal
FROM @xmlDoc.nodes('/*/*') as xml(bar)
SELECT ColumnHeader AS H, ColumnVal AS V
FROM @tmp
which returns:
H V
int 1013
int 1015
int 1016
int 1017
Above code gets data into
@tmp
table. Now, you can insert new or update existing data.
To update, use:
UPDATE SET
QL.LocActiv = 'True',
QL.LocKrt = @LocKrt
FROM QmFlex_Locations AS QL RIGHT JOIN (SELECT CONVERT(INT, ColumnVal) AS LocId FROM @tbl) AS T
ON QL.LocId = T.LocId
or (similar to your query)
UPDATE QmFlex_Locations SET
LocActiv = 'True',
LocKrt = @LocKrt
WHERE LocId IN (SELECT CONVERT(INT, ColumnVal) AS LocId FROM @tmp)
To insert new data, use:
INSERT INTO QmFlex_Locations (LocId, LocActiv, LocKrt, LocWpr, LocMld)
SELECT CONVERT(INT, ColumnVal) AS LocId, 'True' AS LocActiv, @LocKrt AS LocKrt, 0 AS LocWpr, 0 AS LocMld
FROM @tmp
Note: You can call
UPDATE
and
INSERT
query without any
IF
statement ;)