CODEWITHSUNDEEP
sql-serverxml
Srinivasan Rajasekaran
Srinivasan Rajasekaran

Reputation: 45

Insert Xml at Specific Node SQL Server

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?

Upvotes: 3

Views: 101

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

@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

Upvotes: 1

Xedni
Xedni

Reputation: 4695

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

Upvotes: 0

Related Questions