将SQL查询生成的XML文件中添加具有静态值的根元素。

huangapple go评论56阅读模式
英文:

Add root with static values to xml file create by SQL query

问题

以下是您提供的内容的翻译部分:

<STEP-ProductInformation ImportContext="Context1" ContextID="Context1" WorkspaceID="Main" UseContextLocale="false">
    <Products>
        <Product ID="1557505" UserTypeID="CatalogNumber" ParentID="39122206_5183">
            <Name>DVSCTV-HT</Name>
            <Values>
                <Value AttributeID="IDWIndexID">10706063</Value>
                <Value AttributeID="IDW_ChangeFlag">A</Value>
                <Value AttributeID="IDW_StockIndicatorLOV">S</Value>
                <Value AttributeID="IDW_GTIN">00027557663526</Value>
                <MultiValue AttributeID="IDW_Color">
                    <Value>Hot</Value>
                    <Value>Plastic</Value>
                </MultiValue>
            </Values>
        </Product>
    </Products>
</STEP-ProductInformation>

这是您所需的输出,其中有一个根元素包围整个内容。如果您需要任何其他帮助,请随时告诉我。

英文:

The following creates an xml file in the format shown at the bottom. I got a requirement from our 3rd application developer that there needs to be an upper root (? or namespace ?) above the current root. I have zero idea on how to accomplish this.

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    	ProductID nvarchar(25) NULL,
    	[Name] nvarchar(25) NULL,
    	ParentID nvarchar(51) NULL,
    	AttributeType nvarchar(10) NULL,
    	AttributeID nvarchar(255) NULL,
    	AttributeValue nvarchar(4000) NULL
);

INSERT INTO @tbl (ProductID, [Name], ParentID, AttributeType, AttributeID, AttributeValue)
VALUES 
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;Value&#39;,&#39;IDW_EnvironmentalConditions&#39;,&#39;Indoor use only&#39;),
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;Value&#39;,&#39;IDW_Enclosure&#39;,&#39;Plastic&#39;),
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;MultiValue&#39;,&#39;IDW_Color&#39;,&#39;Hot&#39;),
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;MultiValue&#39;,&#39;IDW_Color&#39;,&#39;Purple&#39;), 
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;MultiValue&#39;,&#39;IDW_Color&#39;,&#39;Yellow&#39;);
-- DDL and sample data population, end

SELECT ProductID AS [@ID]
	, &#39;CatalogNumber&#39; AS [@UserTypeID]
	, ParentID AS [@ParentID]
	, Name AS [Name]
	, (SELECT AttributeType, AttributeID, AttributeValue
		FROM @tbl AS c
		WHERE p.ProductID = c.ProductID
			AND p.ParentID = c.ParentID
			AND p.[Name] = c.[Name]
		FOR XML PATH(&#39;r&#39;), TYPE, ROOT(&#39;root&#39;)
).query(&#39;&lt;Values&gt;
{
	for $x in /root/r[AttributeType=&quot;Value&quot;]
	return &lt;Value AttributeID=&quot;{data($x/AttributeID)}&quot;&gt;{data($x/AttributeValue)}&lt;/Value&gt;,
	&lt;MultiValue AttributeID=&quot;{(/root/r[AttributeType=&quot;MultiValue&quot;]/AttributeID/text())[1]}&quot;&gt;
    {
        for $x in /root/r[AttributeType=&quot;MultiValue&quot;]/AttributeValue/text()
        return &lt;Value&gt;{$x}&lt;/Value&gt;
    }
    &lt;/MultiValue&gt;
}
&lt;/Values&gt;&#39;)
FROM @tbl AS p
GROUP BY ProductID, ParentID, [Name]
FOR XML PATH(&#39;Product&#39;), TYPE, ROOT(&#39;Products&#39;);

Here is the formatted output:

&lt;Products&gt;
  &lt;Product ID=&quot;1557505&quot; UserTypeID=&quot;CatalogNumber&quot; ParentID=&quot;39122206_5183&quot;&gt;
    &lt;Name&gt;DVSCTV-HT&lt;/Name&gt;
    &lt;Values&gt;
      &lt;Value AttributeID=&quot;IDWIndexID&quot;&gt;10706063&lt;/Value&gt;
      &lt;Value AttributeID=&quot;IDW_ChangeFlag&quot;&gt;A&lt;/Value&gt;
      &lt;Value AttributeID=&quot;IDW_StockIndicatorLOV&quot;&gt;S&lt;/Value&gt;
      &lt;Value AttributeID=&quot;IDW_GTIN&quot;&gt;00027557663526&lt;/Value&gt;
      &lt;MultiValue AttributeID=&quot;IDW_Color&quot;&gt;
        &lt;Value&gt;Hot&lt;/Value&gt;
        &lt;Value&gt;Plastic&lt;/Value&gt;
      &lt;/MultiValue&gt;
    &lt;/Values&gt;
  &lt;/Product&gt;
&lt;/Products&gt;

But i need a root above this with static values so the output should look like this:

&lt;STEP-ProductInformation ImportContext=&quot;Context1&quot; ContextID=&quot;Context1&quot; WorkspaceID=&quot;Main&quot; UseContextLocale=&quot;false&quot;&gt; 
	&lt;Products&gt;
		&lt;Product ID=&quot;1557505&quot; UserTypeID=&quot;CatalogNumber&quot; ParentID=&quot;39122206_5183&quot;&gt;
			&lt;Name&gt;DVSCTV-HT&lt;/Name&gt;
			&lt;Values&gt;
				&lt;Value AttributeID=&quot;IDWIndexID&quot;&gt;10706063&lt;/Value&gt;
				&lt;Value AttributeID=&quot;IDW_ChangeFlag&quot;&gt;A&lt;/Value&gt;
				&lt;Value AttributeID=&quot;IDW_StockIndicatorLOV&quot;&gt;S&lt;/Value&gt;
				&lt;Value AttributeID=&quot;IDW_GTIN&quot;&gt;00027557663526&lt;/Value&gt;
				&lt;MultiValue AttributeID=&quot;IDW_Color&quot;&gt;
					&lt;Value&gt;Hot&lt;/Value&gt;
					&lt;Value&gt;Plastic&lt;/Value&gt;
				&lt;/MultiValue&gt;
			&lt;/Values&gt;
		&lt;/Product&gt;
	&lt;/Products&gt;
&lt;/STEP-ProductInformation&gt;

Thanks for any help you can provide.

答案1

得分: 0

请尝试以下解决方案。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    ProductID nvarchar(25) NULL,
    [Name] nvarchar(25) NULL,
    ParentID nvarchar(51) NULL,
    AttributeType nvarchar(10) NULL,
    AttributeID nvarchar(255) NULL,
    AttributeValue nvarchar(4000) NULL
);

INSERT INTO @tbl (ProductID, [Name], ParentID, AttributeType, AttributeID, AttributeValue)
VALUES 
('1557505','DVSCTV-HT','39122206_5183','Value','IDW_EnvironmentalConditions','Indoor use only'),
('1557505','DVSCTV-HT','39122206_5183','Value','IDW_Enclosure','Plastic'),
('1557505','DVSCTV-HT','39122206_5183','MultiValue','IDW_Color','Hot'),
('1557505','DVSCTV-HT','39122206_5183','MultiValue','IDW_Color','Purple'), 
('1557505','DVSCTV-HT','39122206_5183','MultiValue','IDW_Color','Yellow');
-- DDL and sample data population, end

SELECT 'Context1' AS [@ImportContext]
    , 'Context1' AS [@ContextID]
    , 'Main' AS [@WorkspaceID]
    , 'false' AS [@UseContextLocale]
, (
SELECT ProductID AS [@ID]
    , 'CatalogNumber' AS [@UserTypeID]
    , ParentID AS [@ParentID]
    , Name AS [Name]
    , (SELECT AttributeType, AttributeID, AttributeValue
        FROM @tbl AS c
        WHERE p.ProductID = c.ProductID
            AND p.ParentID = c.ParentID
            AND p.[Name] = c.[Name]
        FOR XML PATH('r'), TYPE, ROOT('root')
).query('<Values>
{
    for $x in /root/r[AttributeType="Value"]
    return <Value AttributeID="{$x/AttributeID/text()}">{data($x/AttributeValue)}</Value>,
    <MultiValue AttributeID="{(/root/r[AttributeType="MultiValue"]/AttributeID/text())[1]}">
    {
        for $x in /root/r[AttributeType="MultiValue"]/AttributeValue/text()
        return <Value>{$x}</Value>
    }
    </MultiValue>
}
</Values>')
FROM @tbl AS p
GROUP BY ProductID, ParentID, [Name]
FOR XML PATH('Product'), TYPE, ROOT('Products')
)
FOR XML PATH('STEP-ProductInformation'), TYPE;

Output

<STEP-ProductInformation ImportContext="Context1" ContextID="Context1" WorkspaceID="Main" UseContextLocale="false">
  <Products>
    <Product ID="1557505" UserTypeID="CatalogNumber" ParentID="39122206_5183">
      <Name>DVSCTV-HT</Name>
      <Values>
        <Value AttributeID="IDW_EnvironmentalConditions">Indoor use only</Value>
        <Value AttributeID="IDW_Enclosure">Plastic</Value>
        <MultiValue AttributeID="IDW_Color">
          <Value>Hot</Value>
          <Value>Purple</Value>
          <Value>Yellow</Value>
        </MultiValue>
      </Values>
    </Product>
  </Products>
</STEP-ProductInformation>
英文:

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
ProductID nvarchar(25) NULL,
[Name] nvarchar(25) NULL,
ParentID nvarchar(51) NULL,
AttributeType nvarchar(10) NULL,
AttributeID nvarchar(255) NULL,
AttributeValue nvarchar(4000) NULL
);
INSERT INTO @tbl (ProductID, [Name], ParentID, AttributeType, AttributeID, AttributeValue)
VALUES 
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;Value&#39;,&#39;IDW_EnvironmentalConditions&#39;,&#39;Indoor use only&#39;),
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;Value&#39;,&#39;IDW_Enclosure&#39;,&#39;Plastic&#39;),
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;MultiValue&#39;,&#39;IDW_Color&#39;,&#39;Hot&#39;),
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;MultiValue&#39;,&#39;IDW_Color&#39;,&#39;Purple&#39;), 
(&#39;1557505&#39;,&#39;DVSCTV-HT&#39;,&#39;39122206_5183&#39;,&#39;MultiValue&#39;,&#39;IDW_Color&#39;,&#39;Yellow&#39;);
-- DDL and sample data population, end
SELECT &#39;Context1&#39; AS [@ImportContext]
, &#39;Context1&#39; AS [@ContextID]
, &#39;Main&#39; AS [@WorkspaceID]
, &#39;false&#39; AS [@UseContextLocale]
, (
SELECT ProductID AS [@ID]
, &#39;CatalogNumber&#39; AS [@UserTypeID]
, ParentID AS [@ParentID]
, Name AS [Name]
, (SELECT AttributeType, AttributeID, AttributeValue
FROM @tbl AS c
WHERE p.ProductID = c.ProductID
AND p.ParentID = c.ParentID
AND p.[Name] = c.[Name]
FOR XML PATH(&#39;r&#39;), TYPE, ROOT(&#39;root&#39;)
).query(&#39;&lt;Values&gt;
{
for $x in /root/r[AttributeType=&quot;Value&quot;]
return &lt;Value AttributeID=&quot;{data($x/AttributeID)}&quot;&gt;{data($x/AttributeValue)}&lt;/Value&gt;,
&lt;MultiValue AttributeID=&quot;{(/root/r[AttributeType=&quot;MultiValue&quot;]/AttributeID/text())[1]}&quot;&gt;
{
for $x in /root/r[AttributeType=&quot;MultiValue&quot;]/AttributeValue/text()
return &lt;Value&gt;{$x}&lt;/Value&gt;
}
&lt;/MultiValue&gt;
}
&lt;/Values&gt;&#39;)
FROM @tbl AS p
GROUP BY ProductID, ParentID, [Name]
FOR XML PATH(&#39;Product&#39;), TYPE, ROOT(&#39;Products&#39;)
)
FOR XML PATH(&#39;STEP-ProductInformation&#39;), TYPE;

Output

&lt;STEP-ProductInformation ImportContext=&quot;Context1&quot; ContextID=&quot;Context1&quot; WorkspaceID=&quot;Main&quot; UseContextLocale=&quot;false&quot;&gt;
&lt;Products&gt;
&lt;Product ID=&quot;1557505&quot; UserTypeID=&quot;CatalogNumber&quot; ParentID=&quot;39122206_5183&quot;&gt;
&lt;Name&gt;DVSCTV-HT&lt;/Name&gt;
&lt;Values&gt;
&lt;Value AttributeID=&quot;IDW_EnvironmentalConditions&quot;&gt;Indoor use only&lt;/Value&gt;
&lt;Value AttributeID=&quot;IDW_Enclosure&quot;&gt;Plastic&lt;/Value&gt;
&lt;MultiValue AttributeID=&quot;IDW_Color&quot;&gt;
&lt;Value&gt;Hot&lt;/Value&gt;
&lt;Value&gt;Purple&lt;/Value&gt;
&lt;Value&gt;Yellow&lt;/Value&gt;
&lt;/MultiValue&gt;
&lt;/Values&gt;
&lt;/Product&gt;
&lt;/Products&gt;
&lt;/STEP-ProductInformation&gt;

huangapple
  • 本文由 发表于 2023年4月11日 00:51:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/75978997.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定