How to correctly build table from an xml datatype

Question

How to correctly build table from an xml datatype

BuilderBits

SSCertifiable

Points: 6071
More actions
June 3, 2008 at 6:53 pm

#96310

I have a function that accepts an xml data type parameter then I build a table from the nodes.
I am using xml so that I can bulk insert, update, and delete columns values.
Here is the xml function
ALTER FUNCTION [dbo].[SplitList]
(
@list AS XML
)
RETURNS TABLE
AS
RETURN
(
SELECT tempTable.item.value('.', 'VARCHAR(MAX)') AS ProductID
FROM @list.nodes('list/item') tempTable(item)
);
Here is where I use it.
ALTER PROC [dbo].[uspUtbProduct_Options_Delete]
@List XML,
@ProductID INT
AS
SET NOCOUNT ON;
DECLARE @ProductOptionID INT, @Cnt INT;
DECLARE @TableVar table
(ID int identity(1,1) PRIMARY KEY,
ProductID INT NOT NULL
)
INSERT INTO @TableVar(ProductID)
SELECT ProductID FROM [SplitList](@List);
SELECT @Cnt = LEN(ProductID) FROM [SplitList](@List)
WHILE(@Cnt > 0)
BEGIN
SELECT @ProductOptionID = ProductID FROM @TableVar WHERE ID = (@Cnt);
DELETE FROM Production.utbProductUtbProductOption
WHERE ProductID = @ProductID AND ProductOptionID = @ProductOptionID
SET @Cnt = @Cnt-1
END
SET NOCOUNT OFF;
My problem is that no matter how many nodes I pass in I always get 2 for the length.
SELECT @Cnt = LEN(ProductID) FROM [SplitList](@List)
@Cnt always = 2 for some reason.
I suspect that it is the way I have the splitlist designed.
Any suggestions?
Erik
Dam again!

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

AFCC Inc. Com (6/3/2008)
My problem is that no matter how many nodes I pass in I always get 2 for the length.
SELECT @Cnt = LEN(ProductID) FROM [SplitList](@List)
@Cnt always = 2 for some reason.

LEN(ProductID) will equal 2 for any ProductID value between 10 and 99. That's because LEN() is a string function, so ProductID is getting converted to a string first.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

By the way, it does not appear that you need to be using that WHILE loop, and it will perform terribly compared to a set-based approach.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

BuilderBits SSCertifiable Points: 6071 More actions · Answer 3

BuilderBits

SSCertifiable

Points: 6071

June 3, 2008 at 7:56 pm

#823956

Can you show my the set implementation?

Dam again!

BuilderBits SSCertifiable Points: 6071 More actions · Answer 4

Thanks alot!

I just changed the LEN to COUNT(*) and this works great..

Thanks again

Erik

Dam again!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Sure, just replace this:

WHILE(@Cnt > 0)

BEGIN

SELECT @ProductOptionID = ProductID FROM @TableVar WHERE ID = (@Cnt);

DELETE FROM Production.utbProductUtbProductOption

WHERE ProductID = @ProductID AND ProductOptionID = @ProductOptionID

SET @Cnt = @Cnt-1

END

with this:

DELETE FROM Production.utbProductUtbProductOption

WHERE ProductID = @ProductID

AND ProductOptionID =

(SELECT ProductID FROM @TableVar WHERE ID <= @Cnt and ID > 0)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

BuilderBits SSCertifiable Points: 6071 More actions · Answer 6

Thanks Barry !

I did not receive the notification email for some reason.

Thanks

Erik

Dam again!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

No problem.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]