DECLARE @myDoc xml;
select @myDoc = ItemXob from TCRecords where RecordID = '047-002941092';
SELECT @myDoc;
declare @recID char(13), @Relation nvarchar(250), @root nvarchar(50), @Type nvarchar(50);
set @recID = '036-003477548';
set @Relation = 'Photographer';
set @root = 'Person';
set @Type = 'ArchiveDescriptionNameAuthorityRelationships';
-- insert first feature child (no need to specify as first or as last)
SET @myDoc.modify('
insert <RelatedArchiveDescriptionNamedAuthority TargetNumber= "{sql:variable("@recID")}">
<RelationshipType>{sql:variable("@Relation")}</RelationshipType>
<TargetRecordId>{sql:variable("@recID")}</TargetRecordId>
</RelatedArchiveDescriptionNamedAuthority>
into (/Person/ArchiveDescriptionNameAuthorityRelationships)[1]') ;
SELECT @myDoc ;
but how to pass @root and @type variable to into section to replace into (/Person/ArchiveDescriptionNameAuthorityRelationships)[1]') ; ?
According do your description, firstly, your original xml file has problem ,why you just define <ArchiveDescriptionNameAuthorityRelationships/> without anything.I think your xml file is ought to be
Then if you want to replace the into section by @type variable, you have to define the value of parameter you set.However , into section is not allowed to define parameter value in So I suggest that you could directly use @type in into section, set all
the modify section as a parameter ,as a result ,you could just set the xml as a value in the exec. Here is the demo, I hope it could help you.
declare @myDoc xml='
<Place RecordID="048-002954970">
<ArchiveDescriptionNameAuthorityRelationships>
</ArchiveDescriptionNameAuthorityRelationships>
</Place>
'
declare @recID char(13), @Relation nvarchar(250), @root nvarchar(50), @Type nvarchar(50);
set @recID = '036-003477548';
set @Relation = 'Photographer';
set @root = 'Person';
set @Type = 'ArchiveDescriptionNameAuthorityRelationships';
declare @sql nvarchar(max);
set @sql =N'
SET @myDocInner.modify(''
insert <RelatedArchiveDescriptionNamedAuthority TargetNumber= "'+@recID+'">
<RelationshipType>'+@Relation+'</RelationshipType>
<TargetRecordId>'+@recID+'</TargetRecordId>
</RelatedArchiveDescriptionNamedAuthority>
into (/Place/'+@Type+')[1]'') ; select @myDocInner
'
EXEC SP_EXECUTESQL @sql,N'@myDocInner xml',@myDocInner=@myDoc
Member
179 Points
720 Posts
How to add XML Element using SQL
Mar 15, 2019 09:54 AM|shabbir_215|LINK
How to insert below XML element using SQL
Below is my Original XML file
Many Thanks,
Shabbir
Member
179 Points
720 Posts
Re: How to add XML Element using SQL
Mar 15, 2019 10:52 AM|shabbir_215|LINK
I manage to find solution
but how to pass @root and @type variable to into section to replace into (/Person/ArchiveDescriptionNameAuthorityRelationships)[1]') ; ?
Many Thanks
Participant
1300 Points
522 Posts
Re: How to add XML Element using SQL
Mar 18, 2019 05:57 AM|Wei Zhang|LINK
Hi,shabbir
According do your description, firstly, your original xml file has problem ,why you just define <ArchiveDescriptionNameAuthorityRelationships/> without anything.I think your xml file is ought to be
Then if you want to replace the into section by @type variable, you have to define the value of parameter you set.However , into section is not allowed to define parameter value in So I suggest that you could directly use @type in into section, set all the modify section as a parameter ,as a result ,you could just set the xml as a value in the exec. Here is the demo, I hope it could help you.
It shows as below:
Best Regards
Wei
Member
179 Points
720 Posts
Re: How to add XML Element using SQL
Mar 19, 2019 09:41 AM|shabbir_215|LINK
Great Thanks Zhang, You're Superb :)