英文:
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
('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 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="{data($x/AttributeID)}">{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');
Here is the formatted output:
<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>
But i need a root above this with static values so the output should look like this:
<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>
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
('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="{data($x/AttributeID)}">{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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论