Insert Xml at Specific Node SQL Server

Question

I have the following Code,

@MasterXML= '
    <Report MustUnderstand="df" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily">
      <DataSets>
        <DataSet Name="'+@Datasetname+'">
          <Query>
          </Query>
          <Fields>
          <Field>
          </Field>
          </Fields>
        </DataSet>
      </DataSets>
      </Report> '
    WHILE @i <= @Cnt
    BEGIN

    Select @xml=
     (  
      SELECT  @cname AS [@Name]
               ,@cname AS [DataField]
               ,'System.String' AS [rd:TypeName]
        FOR XML PATH('Field')
    );
    SET @i = @i + 1
    SET @MasterXML.modify(' insert sql:variable("@xml") as last into  (Fields)[1] ' )  
    end
    select @MasterXML

I am Generating a XMLat @xml through loop and i want to insert it after

I tried SET @MasterXML.modify(' insert sql:variable("@xml") as last into (Fields)[1] ' ), but the generated fields does not add up.

Can Anyone help me out?

I'm pretty sure, that this can be done much better... You do not tell us, where the @cname is coming from, but this can be done without any loop or .modify() - at least I think so... My suggestion: There are some answers upon this question. If they solve it, please accept the one you like most and use your right to vote. Then set up a new question with some more information what you really try to achieve. This sounds like an xy-problem — Gottfried Lesigang
– Gottfried Lesigang, Commented Mar 28, 2018 at 7:11

Mikael Eriksson · Accepted Answer · 2018-03-28 06:38:56Z

1

@MasterXML has a default namespace http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition so you need to reference the nodes using that namespace.

set @MasterXML.modify('
  declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition";
  insert sql:variable("@xml") as last into  (/Report/DataSets/DataSet/Fields)[1]');

Note that the elements in the XML you are adding to @MasterXML does not use a namespace so you will get xmlns="" added for you for those elements. If you want them to be part of the same namespace you have to specify that in your for xml query.

Add Namespaces to Queries with WITH XMLNAMESPACES

answered Mar 28, 2018 at 6:38

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Xedni · Accepted Answer · 2018-03-28 02:41:49Z

0

Your xpath expression isn't valid. try something like this:

SET @MasterXML.modify(' insert sql:variable("@xml") as last into  (/Report/DataSets/DataSet/Fields)[1] ' )

answered Mar 28, 2018 at 2:41

Xedni

4,7913 gold badges22 silver badges38 bronze badges

2 Comments

Xedni Over a year ago

What do you mean "the fields does not add up"?

Srinivasan Rajasekaran Over a year ago

The @XML part does not adds up to specified node.

Collectives™ on Stack Overflow

Insert Xml at Specific Node SQL Server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related