February 23, 2012 at 4:58 am
Hi
CREATE table #Table1 (ID1 int,ID2 int,ID3 tinyint)
DECLARE @x xml
DECLARE @ID1 int
SET @ID1 = 1
SET @x = '
<Products>
<ProductType>
<ProductTypeID>101</ProductTypeID>
<Product>
<ProductID>1</ProductID>
<ProductID>2</ProductID>
</Product>
</ProductType>
<ProductType>
<ProductTypeID>102</ProductTypeID>
<Product>
<ProductID>5</ProductID>
<ProductID>6</ProductID>
</Product>
</ProductType>
</Products>'
INSERT INTO [dbo].[#Table1]
(
ID1,
ID2,
ID3
)
SELECT
@ID1
,V.y.value('(./ProductID)[1]','int') AS ID2
,V.y.value('(//ProductTypeID)[1]','tinyint') AS ID3
--,V.y.value('.','int') AS ProductID
FROM @x.nodes('/Products/ProductType/Product') AS V(y)
SELECT * FROM #Table1
GO
DROP TABLE #Table1
GO
I want to insert the value from the xml into table ..pls help
February 23, 2012 at 5:09 am
I think i understand your query, and have come up with the following:
INSERT INTO [dbo].[#Table1]
(
ID1,
ID2,
ID3
)
SELECT
@ID1,
w.y.value('(.)[1]','tinyint'),
V.y.value('(ProductTypeID)[1]','tinyint') AS ID3
FROM @x.nodes('/Products/ProductType') AS V(y)
CROSS APPLY v.y.nodes('Product/ProductID') AS w(y)
does that help? if not can you explain a little more on what you are trying to get out of the xml.
February 23, 2012 at 5:42 am
thank you very much...exactly this one i wanted.
One more question
Isn't cross apply is slow...travesing so many tags
February 23, 2012 at 6:06 am
dilipd006 (2/23/2012)
thank you very much...exactly this one i wanted.One more question
Isn't cross apply is slow...travesing so many tags
CROSS APPLY itself works well in my opinion and I've seen the use of it increase performance in some circumstances. There are posts out there knocking CROSS APPLY for being slow, but when the posted issue was delved into deeper, invariably it was seen that it was the query itself that was being slow and not CROSS APPLY.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply