根据其他属性修改XML属性 [英] Modify XML attribute depending on other attribute

问题描述

嘿伙计们，

这是我的问题：

我正在调用一个存储过程，它将xml作为参数。

这里是我发送的xml：

Hey guys,
here's my question:
i'm calling a stored procedure which gets xml as a parameter.
here is the xml i'm sending:

<Root>
  <Categories>
    <TopCategory></TopCategory>
    <MediumCategory></MediumCategory>
    <SubCategory>tomato</SubCategory>
  </Categories>
  <Categories>
    <TopCategory></TopCategory>
    <MediumCategory></MediumCategory>
    <SubCategory>banana</SubCategory>
  </Categories>
  <Categories>
    <TopCategory></TopCategory>
    <MediumCategory></MediumCategory>
    <SubCategory>apple</SubCategory>
  </Categories>
<Root>

SP中的
，我想使用游标和foreach节点迭代节点，

得到< SubCategory>值和搜索另一个表（tblMediumCategory，tblTopCategory）中要插入的值。

最后，我想将xml作为输出返回。

我现在正在做的是将xml转换为表格，并且通过使用游标，我成功地获得了所有的SubCategories。

问题是当我想要更新节点时。我不知道如何设置WHERE子句，我不知道如何告诉sql更新特定的等< TopCategory>其中< SubCategory> = BlaBla。

这是SP到目前为止的样子：

in the SP, i want to iterate the nodes using cursor and foreach node,
getting the <SubCategory> value and serach in another tables (tblMediumCategory, tblTopCategory) for the value to insert.
In the end, i want to return the xml as an output.
what i'm doing now is converting the xml to a table, and by using cursor i'm succesfully able to get all the SubCategories.
The problem is when i want to update the node. i dont know how to set the WHERE clause, i dont know how to tell the sql to update a specific etc <TopCategory> where <SubCategory> = BlaBla.
This is how the SP looks like so far:

ALTER PROCEDURE [dbo].[SP_GetCategories]
@xmlCat xml
AS
BEGIN
--===============================
DECLARE @str nvarchar(max)
DECLARE @tmpMedium nvarchar(50), @tmpTop nvarchar (50)
DECLARE @idoc INT
declare @tmpTbl table(id int, parentId int, nodetype int, localname nvarchar(max), prefix nvarchar(max), namespaceuri nvarchar(max), datatype nvarchar(max), prev int, [text] nvarchar(max))
--===============================
EXEC sys.sp_xml_preparedocument @idoc OUTPUT, @xmlCat
--===============================
INSERT INTO @tmpTbl SELECT * FROM OPENXML (@idoc, '/Root', 2)
--===============================
DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT DISTINCT t.text
FROM @tmpTbl t
--===============================
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @str
WHILE @@FETCH_STATUS = 0
BEGIN 
--===============================
SELECT @tmpMedium = m.CategoryName, @tmpTop = t.CategoryName 
FROM tblSubSources s
JOIN tblMediumSources m
ON s.MediumSourceId = m.CategoryId
JOIN tblTopSources t
ON m.TopCategoryId = t.CategoryId
WHERE s.SubSourceName = @str
--===============================
SET  @xmlCat.modify('replace value of (/Rows/Categorys/@TopCategory)[1] with sql:variable("@tmpTop")')
************************************This is where the <WHERE> Clause suppouse to be!!!!!!!!!!!!!!!!**********************************
--===============================
FETCH NEXT FROM MY_CURSOR INTO @str
--===============================
END
--===============================
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
--===============================
EXEC SYS.SP_XML_REMOVEDOCUMENT @idoc
--===============================
END

有人可以帮我吗？



我尝试过：



.............................................

Can anyone help me please?

What I have tried:

.............................................

推荐答案

虽然可以在SQL Server的最新版本中使用XQuery（自2012年以来我认为）你正在尝试的是高剂量的开销，因此效率低下。



我会将XML插入@temp表，将其与其他表连接以更新它，然后从带有FOR XML AUTO，ROOT（'Root'）的@temp表中选择因为您说要将XML作为输出返回。



虽然我不知道数据库的设计，但只关注这些想法。这段代码至少应该是你需要的90％

While possible to use XQuery in SQL Server's latest versions (since 2012 I think) what you are trying has a high dose of overhead and is therefore inefficient.

I would insert the XML into a @temp table, join it with the other tables to update it, and then select from the @temp table with FOR XML AUTO, ROOT('Root') since you're saying you want to return XML as an output.

While I don't know the design of your database, focus just on the ideas. This code should be at least 90% what you need

/*

This is pretty much your XML that you want to update, right?

<Root>
  <Categories>
    <TopCategory></TopCategory>
    <MediumCategory></MediumCategory>
    <SubCategory>tomato</SubCategory>
  </Categories>
  <Categories>
    <TopCategory></TopCategory>
    <MediumCategory></MediumCategory>
    <SubCategory>banana</SubCategory>
  </Categories>
  <Categories>
    <TopCategory></TopCategory>
    <MediumCategory></MediumCategory>
    <SubCategory>apple</SubCategory>
  </Categories>
</Root>
*/


ALTER PROCEDURE [dbo].[SP_GetCategories]
@xmlCat xml
AS
BEGIN


DECLARE @idoc INT;

DECLARE @tmpTbl table(
id INT IDENTITY(1, 1) PRIMARY KEY --you always want a uniquer identifier, right?
, TopCategory NVARCHAR(100)
, MediumCategory NVARCHAR(100)
, SubCategory NVARCHAR(100)
);


EXEC sys.sp_xml_preparedocument @idoc OUTPUT, @xmlCat;

INSERT INTO @tmpTbl (TopCategory, MediumCategory, SubCategory)
SELECT TopCategory, MediumCategory, SubCategory 
FROM OPENXML (@idoc, '/Root/Categories', 2)
WITH (
TopCategory NVARCHAR(100) 'TopCategory' --Mapping to the exact element name is optional
, MediumCategory NVARCHAR(100) 'MediumCategory' --if your elements are always present on the right place
, SubCategory NVARCHAR(100) 'SubCategory'
);

--you already have your data, no need to keep this in the engine
EXEC SYS.SP_XML_REMOVEDOCUMENT @idoc;


--I'M NOT 100% SURE THIS ONE IS RIGHT, YOU'LL BE THE BEST JUDGE
--JUST FOCUS ON THE IDEA BEHIND
WITH Categories AS (
SELECT m.CategoryName AS MediumCategory, t.CategoryName AS TopCategory, s.SubSourceName
FROM tblSubSources s
JOIN tblMediumSources m ON s.MediumSourceId = m.CategoryId
JOIN tblTopSources t ON m.TopCategoryId = t.CategoryId
)
UPDATE temp
SET TopCategory = c.TopCategory
	, MediumCategory = c.MediumCategory
FROM @tmpTbl temp
INNER JOIN Categories c ON temp.SubCategory = c.SubSourceName;


--NOW your @tmpTbl is updated with the values you needed
--time to output the table as XML
SELECT 
TopCategory
, MediumCategory
, SubCategory
FROM @tmpTbl AS Categories
FOR XML AUTO, ELEMENTS, ROOT('Root')


END

提示

我最近刚刚与XML文档发生过激烈争吵，但文件赢了。

映射元素时，它不起作用。

将XML传递给存储过程也无法正常工作。



XML区分大小写，因此我使用RowID和RowId之类的差异来调用元素名称错误。

映射元素时要非常小心。

Tip
I just recently had a big fight with a XML document, but the document won.
When mapping elements it just wouldn't work.
Passing XML to a stored procedure was not working either.

XML is case-sensitive so I was calling the elements the wrong names with differences as simple as 'RowID' and 'RowId'.
Be very careful when mapping elements.

这篇关于根据其他属性修改XML属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！