如何将XML字符串替换为另一个XML字符串

Question

<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>

我想把上面的XML转换成以下预期的输出:

<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>

我在SQL中检查了替换函数，但元素如"Type">

我试图获取所有属性和它的值也使用下面的几个XmlQueries，但没有任何效果:

--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

Answer 1

请记住这两件事:Microsoft SQL Server是一个非常强大的工具，不仅是因为T-SQL，还因为它集成的工具。2. XQuery,XPath是您的朋友。(:

因此，如果@Input是包含第一个Xml数据的变量，则可以运行以下Select语句，并在Xml变量上应用XPathquery:

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

结果将是一个标量Xml值:

<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>

您可以在微软网站上阅读有关XQuery的所有内容(特别是查找Xml构造，并学习操作符/函数)。