How to add XML Element using SQL

Reply

shabbir_215

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

<ArchiveDescriptionNameAuthorityRelationships>
    <RelatedArchiveDescriptionNamedAuthority TargetNumber="047-002941092">
      <RelationshipType>Photographer</RelationshipType>
      <TargetRecordId>047-002941092</TargetRecordId>
    </RelatedArchiveDescriptionNamedAuthority>
</ArchiveDescriptionNameAuthorityRelationships>

Below is my Original XML file

<Place RecordID="048-002954970">

 <ArchiveDescriptionNameAuthorityRelationships/>

</Place>

Many Thanks,

Shabbir

Reply

shabbir_215

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

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]') ; ?

Many Thanks

Reply

Wei Zhang

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

<Place RecordID="048-002954970">

 <ArchiveDescriptionNameAuthorityRelationships>
 </ArchiveDescriptionNameAuthorityRelationships>

</Place>

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

It shows as below: