Modify XML attribute depending on other attribute

Question

0.00/5 (No votes)

See more:

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:

HTML

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

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:

SQL

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:

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

Posted 21-Feb-17 9:15am

oronsultan

Updated 21-Feb-17 15:19pm

Patrice T

v3

Add a Solution

Comments

Homero Rivera 21-Feb-17 21:58pm

Comment, your XML is not accurately closed. the last element should be /Root

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Homero Rivera · Accepted Answer · 2017-02-21T15:19:00

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

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.