Problem inserting XML data

Have SP that uses an xml input variable that the SP iterates over,
inserting the attributes in the xml into the db. This all works fine
until I try to insert embedded xml.
Example:
declare @.in xml
set @.in = '<input Manufacturer = "Painkiller1" Dosage = "50" otherstuff
= "<data><field key="ingredients">aspirin</field></data>"/>'
exec #aspirin_insertSP @.input = @.in
...and here's the create statement on the SP:
CREATE PROCEDURE #aspirin_insertSP
@.input xml
AS
DECLARE
@.Manufacturer varchar(50),
@.Dosage int,
@.otherstuff xml
INSERT INTO Aspirin (Manufacturer, Dosage, otherstuff)
select
Manufacturer = T.c.value('@.Manufacturer', 'varchar(50)'),
Dosage = T.c.value('@.Dosage', 'int'),
thestuff = T.c.value('@.thestuff','varchar(5000)')
from @.input.nodes('/input') T(c)
I've tried all manner of escape characters and CDATA combos to no
avail. Does anyone have any ideas/experience with anything like this?
Surely someone else has run into this before.
Any help would be much appreciated
You need to escape out less-than, greater-than and double quotes
inside the 'otherstuff' attribute (the embedded xml).
Also
thestuff = T.c.value('@.thestuff','varchar(5000)')
should be
otherstuff = T.c.value('@.otherstuff ','varchar(5000)')
|||Also you can get SQL Server to escape the attribute
for you by doing this
declare @.innerXML varchar(1000)
set @.innerXML='<data><field key="ingredients">aspirin</field></data>'
declare @.input xml
set @.input = '<input Manufacturer = "Painkiller1" Dosage = "50" />'
set @.input.modify('
insert attribute otherstuff {sql:variable("@.innerXML")}
into (/input)[1]')
|||Hello rbnbigd@.msn.com,

> Have SP that uses an xml input variable that the SP iterates over,
> inserting the attributes in the xml into the db. This all works fine
> until I try to insert embedded xml.
Correct. Literal XML cannot be embedded within an attribute. That's one of
the XML rules in general. You can have embedded XML within a text node, so
something like this should work:
declare @.in xml,@.in2 xml
set @.in = '<input Manufacturer = "Painkiller1" Dosage = "50"><![CDATA[<data><field
key="ingredients">aspirin</field></data>]]></input>'
insert into Aspirin(Manufacturer,Dosage,otherstuff)
select @.in.value('(/input/@.Manufacturer)[1]','varchar(20)'),@.in.value('(/input/@.Dosage)[1]','int'),@.in.value('(/input/text())[1]','varchar(max)')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Now if this is XML, why would you want to have it in string format anyway
and not just XML?
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7420bc68c8210fb6695960@.news.microsoft.co m...
> Hello rbnbigd@.msn.com,
>
> Correct. Literal XML cannot be embedded within an attribute. That's one of
> the XML rules in general. You can have embedded XML within a text node, so
> something like this should work:
> declare @.in xml,@.in2 xml
> set @.in = '<input Manufacturer = "Painkiller1" Dosage =
> "50"><![CDATA[<data><field
> key="ingredients">aspirin</field></data>]]></input>'
> insert into Aspirin(Manufacturer,Dosage,otherstuff)
> select
> @.in.value('(/input/@.Manufacturer)[1]','varchar(20)'),@.in.value('(/input/@.Dosage)[1]','int'),@.in.value('(/input/text())[1]','varchar(max)')
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>