I've used a table valued parameter before, but I'm not sure how to use xml.
I do not know the best way to format my xml but I think I would try this:
<Car>
<Name>BMW</Name>
<Color>Red</Color>
</Car>
Then I would pass the xml (one or more car) to the stored procedure and it would insert one row for each car I pass (with the name going in a name column etc..)
Does anyone know how to write the stored procedure? (I'd usually try it myself but I don't have much time for testing T_T)
You can shred the XML using the nodes function:
CREATE PROC ShredXML (@x xml)
AS BEGIN
INSERT INTO TBL_TARGET (Name, Color)
SELECT
x.y.value( 'Name[1]', 'VARCHAR(20)' ) AS Name,
x.y.value( 'Color[1]', 'VARCHAR(20)' ) AS Color
FROM @x.nodes('cars/car') x(y)
END
exec ShredXML @x = N'<cars><car><Name>BMW</Name><Color>Red</Color></car><car><Name>Audi</Name><Color>Green</Color></car></cars>'
alter PROC ShredXML (@x xml)
AS
BEGIN
If not exists (Select * from TBL_TARGET )
begin
INSERT INTO TBL_TARGET (Name, Color)
SELECT x.y.value( 'Name[1]', 'VARCHAR(20)' )AS Name,x.y.value( 'Color[1]','VARCHAR(20)')AS Color FROM @x.nodes('cars/car') x(y)
end
END
exec ShredXML @x = N'<cars><car><Name>BMW</Name><Color>Red</Color></car><car><Name>Audi</Name><Color>Green</Color></car></cars>'
