How to replace XML string into another XML string

<recurrence>
  <interval>1</interval>
  <unit>O</unit>
  <firsttime>2021-02-12T17:42:00</firsttime>
  <lasttime>1900-01-01T12:00:00</lasttime>
</recurrence>
<output>
  <outputformat>TXT</outputformat>
  <delimiter>,</delimiter>
  <filename>testfile1</filename>
  <path>\\AIR-LAP-700053\TestGEMOutput</path>
  <appendtofile />
  <content>
    <type>NORMAL</type>
    <storedprocedure />
    <outputitems>
      <outputitem>
        <field>
          <tablename>VW_DOCUMENT</tablename>
          <fieldname>GUID</fieldname>
        </field>
        <format>
          <type>CHAR</type>
          <specification />
        </format>
      </outputitem>
      <outputitem>
        <field>
          <tablename>VW_DOCUMENT</tablename>
          <fieldname>PHYSICAL_DOC_GUID</fieldname>
        </field>
        <format>
          <type>CHAR</type>
          <specification />
        </format>
      </outputitem>
      <outputitem>
        <field>
          <tablename>VW_DOCUMENT</tablename>
          <fieldname>DOC_TYPE</fieldname>
        </field>
        <format>
          <type>CHAR</type>
          <specification />
        </format>
      </outputitem>
    </outputitems>
    <criteria>
      <criterion>
        <field>
          <tablename>VW_DOCUMENT</tablename>
          <fieldname>DOC_TYPE</fieldname>
        </field>
        <restriction>
          <type>=</type>
          <data>Default</data>
          <functioncode />
        </restriction>
      </criterion>
    </criteria>
  </content>
</output>

I want convert above XML into the following expected output:

<ExportJobDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Recurrence>
    <Interval>1</Interval>
    <Unit>W</Unit>
    <FirstTime>2021-02-19T12:36:00Z</FirstTime>
    <LastTime>2021-02-24T12:36:00Z</LastTime>
  </Recurrence>
  <Output>
    <OutputFormat>TXT</OutputFormat>
    <Delimiter>/</Delimiter>
    <FileName>ColdIndexLog</FileName>
    <Path>\\MUM-LAP-1092\3PrtyUpgImages\Images\Data\cold\Logs</Path>
    <OverwriteFile>O</OverwriteFile>
    <Content>
      <ContentType>NORMAL</ContentType>
      <OutputItems>
        <ExportJobOutputItem>
          <Field>
            <TableName>Document</TableName>
            <FieldName>Document / Doc_Ref</FieldName>
          </Field>
          <Format>
            <Specification />
          </Format>
        </ExportJobOutputItem>
        <ExportJobOutputItem>
          <Field>
            <TableName>Document</TableName>
            <FieldName>Document / Doc_Type</FieldName>
          </Field>
          <Format>
            <Specification />
          </Format>
        </ExportJobOutputItem>
      </OutputItems>
      <Criteria>
        <ExportJobCriterion>
          <Field>
            <TableName>Document </TableName>
            <FieldName>Document / Doc_Type</FieldName>
          </Field>
          <Restriction>
            <RestrictionType>Less than</RestrictionType>
            <Data>ABC</Data>
          </Restriction>
        </ExportJobCriterion>
      </Criteria>
    </Content>
  </Output>
</ExportJobDefinition>

I have check with replace function in SQL but element like "Type" inside XML is exist in different xml parent tags.

I have tried to fetch all attributes and its values also use couple of below XmlQueries but nothing works out:

--DECLARE @temp table (TableName VARCHAR(MAX), FieldName varchar(max))
--INSERT INTO @temp
SELECT tablename = Node.Data.value('(tablename)[1]', 'VARCHAR(MAX)'),
       fieldname = Node.Data.value('(fieldname)[1]', 'VARCHAR(MAX)')
FROM    @xmlData.nodes('/output/content/outputitems/outputitem/field') Node(Data)

--select * from @temp

--select * from @temp FOR XML PATH('')

--DECLARE @temp1 table (Type VARCHAR(MAX), Specification varchar(max))
--INSERT INTO @temp1
SELECT [type] = Node.Data.value('(type)[1]', 'VARCHAR(MAX)'),
       specification = Node.Data.value('(specification)[1]', 'VARCHAR(MAX)')
FROM    @xmlData.nodes('/output/content/outputitems/outputitem/format') Node(Data)

--select * from @temp1

--select * from @temp FOR XML PATH('')


--WITH R AS (
--SELECT
--    ElementName = T.x.value('local-name(.)', 'nvarchar(255)'),
--    ElementValue = T.x.value('text()[1]', 'nvarchar(255)'),

--    AttrName = R.x.value('local-name(.)', 'nvarchar(255)'),
--    AttrValue = R.x.value('(.)[1]', 'nvarchar(255)')
--FROM
--    @xmlData.nodes('//*') AS T(x)
--    OUTER APPLY
--    T.x.nodes('@*') AS R(x)
--)
--SELECT
--  CASE WHEN ElementValue IS NULL THEN AttrName ELSE ElementName END AS [Name],
--    COALESCE(ElementValue, AttrValue) AS [Value]
--FROM
--  R
--WHERE
--  ElementValue IS NOT NULL
--  OR AttrValue IS NOT NULL
--GO

                         

1 answer

  • answered 2021-02-19 09:18 Rikki

    Remember these two things: 1. Microsoft SQL Server is an extremely powerful tool, not only because of T-SQL, but also because of the tools it integrates with. 2. XQuery & XPath are your friends. (:

    So if @Input is a variable containing your first Xml data, you can run the following Select statement, and apply an XPath query on your Xml variable:

    Declare @Input Xml = '<recurrence>
      <interval>1</interval>
      <unit>O</unit>......
    .......
    </recurrence>'
    
    Select  @Input.query('
    <ExportJobDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Recurrence>
        <Interval>{ data(/recurrence/interval[1]) }</Interval>
        <Unit>{ data(/recurrence/unit[1]) }</Unit>
        <FirstTime>{ data(/recurrence/firsttime[1]) }</FirstTime>
      </Recurrence>
      <Output>
        <OutputFormat>TXT</OutputFormat>
        <Delimiter>/</Delimiter>
        <FileName>ColdIndexLog</FileName>
        <Path>\\MUM-LAP-1092\3PrtyUpgImages\Images\Data\cold\Logs</Path>
        <OverwriteFile>O</OverwriteFile>
        <Content>
          <ContentType>NORMAL</ContentType>
          <OutputItems>
        {
            for $i in /output[1]/content[1]/outputitems[1]/outputitem
              return <ExportJobOutputItem>
                  <Field>
                    <TableName>{ data($i/field[1]/tablename) }</TableName>
                    <FieldName>{ data($i/field[1]/fieldname) }</FieldName>
                  </Field>
                  <Format>
                    <Specification />
                  </Format>
                  </ExportJobOutputItem>
        }
          </OutputItems>
        </Content>
      </Output>
    </ExportJobDefinition>
    ')
    

    And the outcome will be an scalar Xml value:

        <ExportJobDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Recurrence>
        <Interval>1</Interval>
        <Unit>O</Unit>
        <FirstTime>2021-02-12T17:42:00</FirstTime>
      </Recurrence>
      <Output>
        <OutputFormat>TXT</OutputFormat>
        <Delimiter>/</Delimiter>
        <FileName>ColdIndexLog</FileName>
        <Path>\\MUM-LAP-1092\3PrtyUpgImages\Images\Data\cold\Logs</Path>
        <OverwriteFile>O</OverwriteFile>
        <Content>
          <ContentType>NORMAL</ContentType>
          <OutputItems>
            <ExportJobOutputItem>
              <Field>
                <TableName>VW_DOCUMENT</TableName>
                <FieldName>GUID</FieldName>
              </Field>
              <Format>
                <Specification />
              </Format>
            </ExportJobOutputItem>
            <ExportJobOutputItem>
              <Field>
                <TableName>VW_DOCUMENT</TableName>
                <FieldName>PHYSICAL_DOC_GUID</FieldName>
              </Field>
              <Format>
                <Specification />
              </Format>
            </ExportJobOutputItem>
            <ExportJobOutputItem>
              <Field>
                <TableName>VW_DOCUMENT</TableName>
                <FieldName>DOC_TYPE</FieldName>
              </Field>
              <Format>
                <Specification />
              </Format>
            </ExportJobOutputItem>
          </OutputItems>
        </Content>
      </Output>
    </ExportJobDefinition>
    

    You can read all about XQuery (specially look for Xml construction, and learn the operators/functions) here on Microsoft's website.