How to replace XML string into another XML string

by 

<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-700053TestGEMOutput</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-10923PrtyUpgImagesImagesDatacoldLogs</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
Contents hide
Answers:
Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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-10923PrtyUpgImagesImagesDatacoldLogs</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-10923PrtyUpgImagesImagesDatacoldLogs</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.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x