SQL and XML
LsK_Lele
I'm upgrading to SQL 2005, and I would like to change some procedures
and start using SQL XML features.
I have an XML parameter like this:
<root>
<products>
<product>
<id>1</id>
<name>pc 1</name>
<components>
<component>
<id>1</id>
<quantity>2</quantity>
<description>this is a description</description>
</component>
--etc...
</components>
</product>
<product>
<id>2</id>
<name>pc 2</name>
<components>
<component>
<id>1</id>
<quantity>1</quantity>
<description>this is a description</description>
</component>
--etc...
</components>
</product>
--etc...
</products>
</root>
With this data, I have to loop for each product, insert it on the
"Products" table (or update it, if exists), then loop again for each
product component (and here, again, insert or update it).
My stored procedure:
--------------------------
CREATE PROCEDURE myProcedure @MyXML xml
AS
BEGIN
DECLARE @DocHandle int
CREATE TABLE #Tmp (
[ComponentID] int,
[ComponentName] varchar(50)
)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @MyXML
INSERT INTO #Tmp (ComponentID, ComponentName) SELECT id, name FROM
OPENXML (@DocHandle, '/products/product', 2)
WITH (id int, name varchar(50))
EXEC sp_xml_removedocument @DocHandle
END
GO
--------------------------
1) Is there any way to avoid using temporary tables? (there may be
thousands of products, each one with 20/30 components)
2) How can I open again the XML and retrieve components data for each
product?
Thank you!
sloan
Here is a sample procedure. You'll have to alter the xpath's a little.
I'd put the xml into 2 @variableTables. One for the Product(s) and one
variable table for the Component(s).
You'll need the parent product_id for each component as well.
Here is the sample:::::::which should work against the Northwind database
...as is.
You can experiment with #temp tables instead of variable tables.
If you didn't have the parent child relationship of Product/Component, you
could go straight into the dbo.RealTables from the OPENXML, but I've used
@variableTables and #temp tables with success, for ~alot of rows.
Try a 2 meg, 4 meg xml file and see how that goes.
I like the @variableTable approach because it makes it easier to debug and
test.
/*
--Highlight the text within this comment range and run it manually to test
your code.
declare @rowcount int
EXEC dbo.uspProductUpdate
'
<?xml version="1.0" standalone="yes"?>
<ProductDS>
<Product>
<ProductID>3</ProductID>
<ProductName>Vermont Syrup</ProductName>
<SupplierID>1</SupplierID>
<CategoryID>2</CategoryID>
<QuantityPerUnit>12 - 550 ml jugs</QuantityPerUnit>
<UnitPrice>105</UnitPrice>
<UnitsInStock>13</UnitsInStock>
<UnitsOnOrder>150</UnitsOnOrder>
<ReorderLevel>25</ReorderLevel>
<Discontinued>false</Discontinued>
</Product>
</ProductDS>
' , @rowcount output
*/
if exists (select * from sysobjects
where id = object_id('uspProductUpdate') and sysstat & 0xf = 4)
drop procedure uspProductUpdate
GO
CREATE PROCEDURE dbo.uspProductUpdate (
@xml_doc TEXT ,
@numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @hdoc INT -- handle to XML doc
DECLARE @errorTracker int -- used to "remember" the @@ERROR
DECLARE @updateRowCount int
DECLARE @insertRowCount int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @productupdate TABLE (
-- identityid int IDENTITY (1,1) ,
productId int ,
productName varchar(40) ,
supplierId int ,
categoryId int ,
quantityPerUnit varchar(20) ,
unitPrice money ,
unitsInStock smallint ,
unitsOnOrder smallint ,
reorderLevel smallint ,
discontinued bit ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @hdoc(with is the holder for
@xml_doc), and put it IN a variableTable
INSERT @productupdate
(
productId ,
productName ,
supplierId ,
categoryId ,
quantityPerUnit ,
unitPrice ,
unitsInStock ,
unitsOnOrder ,
reorderLevel ,
discontinued ,
alreadyExists
)
SELECT
ProductID ,
ProductName ,
SupplierId ,
CategoryId ,
QuantityPerUnit ,
UnitPrice ,
UnitsInStock ,
UnitsOnOrder ,
ReorderLevel ,
Discontinued ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@hdoc, '/ProductDS/Product', 2) WITH (
productId int ,
productName varchar(40) ,
supplierId int ,
categoryId int ,
quantityPerUnit varchar(20) ,
unitPrice money ,
unitsInStock smallint ,
unitsOnOrder smallint ,
reorderLevel smallint ,
discontinued bit ,
alreadyExists bit
)
--Remove the handle to the XML document, since we're done with using the
xmlDoc
EXEC sp_xml_removedocument @hdoc
END
--End XML usage
--lets differeniate between existing (update) and new ones (insert)
Update @productupdate
SET
alreadyExists = 1
FROM
@productupdate pu , dbo.Products p
WHERE
p.ProductID = pu.productId
--temp code here comment these lines out later
Print ' all records from @productupdate'
select * from @productupdate
print ''
SET NOCOUNT OFF
Update
dbo.Products
set
--ProductID = pu.productId ,
ProductName = pu.productName ,
SupplierID = pu.supplierId ,
CategoryID = pu.categoryId ,
QuantityPerUnit = pu.quantityPerUnit ,
UnitPrice = pu.unitPrice ,
UnitsInStock = pu.unitsInStock ,
UnitsOnOrder = pu.unitsOnOrder ,
ReorderLevel = pu.reorderLevel ,
Discontinued = pu.discontinued
FROM
@productupdate pu , dbo.Products p
WHERE
p.ProductID = pu.productId
AND
pu.alreadyExists <> 0
Select @updateRowCount = @@ROWCOUNT
--temp code here comment these lines out later
Print '@updateRowCount value '
Print @updateRowCount
print ''
INSERT INTO dbo.Products
(
--productId,
productName ,
supplierId ,
categoryId ,
quantityPerUnit ,
unitPrice ,
unitsInStock ,
unitsOnOrder ,
reorderLevel ,
discontinued
)
Select
--ProductID ,
ProductName ,
SupplierId ,
CategoryId ,
QuantityPerUnit ,
UnitPrice ,
UnitsInStock ,
UnitsOnOrder ,
ReorderLevel ,
Discontinued
FROM
@productupdate
WHERE
alreadyExists = 0
Select @insertRowCount = @@ROWCOUNT
--temp code here
Print '@insertRowCount value '
Print @insertRowCount
print ''
select @numberRowsAffected = @insertRowCount + @updateRowCount
--select * from dbo.Products
SET NOCOUNT OFF
GO
"LsK_Lele" <nos...@nospam.com> wrote in message
news:473b1ea7$0$36442$4faf...@reader5.news.tin.it...
sloan
source file.
Here is the corrected (and working) version.
And here are 2 microsoft articles:
http://support.microsoft.com/kb/315968
http://msdn2.microsoft.com/en-us/library/aa226522(SQL.80).aspx
What I've done below is the "fined tuned" work I've done based on what I
read at the first KB article .... that I read so long ago. ( Summer 2004??)
/*
--Highlight the text within this comment range and run it manually to test
your code.
declare @rowcount int
EXEC dbo.uspProductUpdate
'
<?xml version="1.0" standalone="yes"?>
<ProductDS>
<Product>
<ProductID>80</ProductID>
<ProductName>Vermont Syrup</ProductName>
<SupplierID>1</SupplierID>
<CategoryID>2</CategoryID>
<QuantityPerUnit>12 - 550 ml jugs</QuantityPerUnit>
<UnitPrice>105</UnitPrice>
<UnitsInStock>13</UnitsInStock>
<UnitsOnOrder>150</UnitsOnOrder>
<ReorderLevel>25</ReorderLevel>
<Discontinued>true</Discontinued>
</Product>
</ProductDS>
' , @rowcount output
Select * from dbo.Products
*/
drop procedure uspProductUpdate
GO
CREATE PROCEDURE dbo.uspProductUpdate (
@xml_doc TEXT ,
@numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @updateRowCount int
DECLARE @insertRowCount int
DECLARE @productupdate TABLE (
productId int ,
productName varchar(40) ,
supplierId int ,
categoryId int ,
quantityPerUnit varchar(20) ,
unitPrice money ,
unitsInStock smallint ,
unitsOnOrder smallint ,
reorderLevel smallint ,
discontinued bit ,
alreadyExists bit DEFAULT 0
)
INSERT @productupdate
(
productId ,
productName ,
supplierId ,
categoryId ,
quantityPerUnit ,
unitPrice ,
unitsInStock ,
unitsOnOrder ,
reorderLevel ,
discontinued ,
alreadyExists
)
SELECT
ProductID ,
ProductName ,
SupplierId ,
CategoryId ,
QuantityPerUnit ,
UnitPrice ,
UnitsInStock ,
UnitsOnOrder ,
ReorderLevel ,
Discontinued ,
0
FROM
productId int './ProductID' , --<< Xml is case sensitive, so be careful
productName varchar(40) './ProductName' , --<< Xml is case sensitive, so be
careful
supplierId int './SupplierID' , --<< Xml is case sensitive, so be careful
categoryId int './CategoryID' ,
quantityPerUnit varchar(20) './QuantityPerUnit' ,
unitPrice money './UnitPrice' ,
unitsInStock smallint './UnitsInStock' ,
unitsOnOrder smallint './UnitsOnOrder' ,
reorderLevel smallint './ReorderLevel' ,
discontinued bit './Discontinued' ,
alreadyExists bit
)
--Remove the handle to the XML document, since we're done with using the
xmlDoc
EXEC sp_xml_removedocument @hdoc
--End XML usage
Mikhail Berlyant
products.product.value('id[1]', 'int') as productID,
products.product.value('name[1]', 'varchar(20)') as productName
from
@par.nodes('/root/products/product') products(product)
select
components.component.value('../../id[1]', 'int') as productID,
components.component.value('id[1]', 'int') as componentID,
components.component.value('quantity[1]', 'varchar(20)') as
componentQuantity,
components.component.value('description[1]', 'varchar(20)') as
componentDescription
from
@par.nodes('/root/products/product/components/component')
components(component)
--
Mikhail Berlyant
Senior Data Architect
MySpace.com
"LsK_Lele" <nos...@nospam.com> wrote in message
news:473b1ea7$0$36442$4faf...@reader5.news.tin.it...
Mikhail Berlyant
declare @par xml
set @par = '
<root>
<products>
<product>
<id>1</id>
<name>pc 1</name>
<components>
<component>
<id>1</id>
<quantity>21</quantity>
<description>this is a description</description>
</component>
<component>
<id>2</id>
<quantity>22</quantity>
<description>this is a description</description>
</component>
<component>
<id>3</id>
<quantity>23</quantity>
<description>this is a description</description>
</component>
</components>
</product>
<product>
<id>2</id>
<name>pc 2</name>
<components>
<component>
<id>1</id>
<quantity>1</quantity>
<description>this is a description</description>
</component>
--etc...
</components>
</product>
</products>
</root>
'
select
products.product.value('id[1]', 'int') as productID,
products.product.value('name[1]', 'varchar(20)') as productName
from
@par.nodes('/root/products/product') products(product)
select
components.component.value('../../id[1]', 'int') as productID,
components.component.value('id[1]', 'int') as componentID,
components.component.value('quantity[1]', 'varchar(20)') as
componentQuantity,
components.component.value('description[1]', 'varchar(20)') as
componentDescription
from
@par.nodes('/root/products/product/components/component')
components(component)
Mikhail
"Mikhail Berlyant" <tnay...@ecapsym.moc> wrote in message
news:%23kY$GbuJIH...@TK2MSFTNGP03.phx.gbl...
LsK_Lele
> missed whole story:
> components.component.value('description[1]', 'varchar(20)') as
> componentDescription
Hi,
Thank you for your reply... I tried this solution, but I cannot use
XQueries because this field type is ntext, and I found that
"value(xquery, type)" does not support text/ntext fields.
Any other solution?
Thank you!
sloan
Here it is revised for a FakeText entry.
/*
--Highlight the text within this comment range and run it manually to test
your code.
declare @rowcount int
EXEC dbo.uspProductUpdate
'
<?xml version="1.0" standalone="yes"?>
<ProductDS>
<Product>
<ProductID>80</ProductID>
<ProductName>Vermont Syrup</ProductName>
<SupplierID>1</SupplierID>
<CategoryID>2</CategoryID>
<QuantityPerUnit>12 - 550 ml jugs</QuantityPerUnit>
<UnitPrice>105</UnitPrice>
<UnitsInStock>13</UnitsInStock>
<UnitsOnOrder>150</UnitsOnOrder>
<ReorderLevel>25</ReorderLevel>
<Discontinued>true</Discontinued>
<FakeText>ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,ABC,123,</FakeText>
</Product>
</ProductDS>
' , @rowcount output
Select * from dbo.Products
*/
drop procedure uspProductUpdate
GO
CREATE PROCEDURE dbo.uspProductUpdate (
@xml_doc TEXT ,
@numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @updateRowCount int
DECLARE @insertRowCount int
DECLARE @productupdate TABLE (
productId int ,
productName varchar(40) ,
supplierId int ,
categoryId int ,
quantityPerUnit varchar(20) ,
unitPrice money ,
unitsInStock smallint ,
unitsOnOrder smallint ,
reorderLevel smallint ,
discontinued bit ,
FakeText text ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @hdoc(with is the holder for
@xml_doc), and put it IN a variableTable
INSERT @productupdate
(
productId ,
productName ,
supplierId ,
categoryId ,
quantityPerUnit ,
unitPrice ,
unitsInStock ,
unitsOnOrder ,
reorderLevel ,
discontinued ,
FakeText ,
alreadyExists
)
SELECT
ProductID ,
ProductName ,
SupplierId ,
CategoryId ,
QuantityPerUnit ,
UnitPrice ,
UnitsInStock ,
UnitsOnOrder ,
ReorderLevel ,
Discontinued ,
FakeText ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@hdoc, '/ProductDS/Product', 2) WITH (
productId int './ProductID' , --<< Xml is case sensitive, so be careful
productName varchar(40) './ProductName' , --<< Xml is case sensitive, so be
careful
supplierId int './SupplierID' , --<< Xml is case sensitive, so be careful
categoryId int './CategoryID' ,
quantityPerUnit varchar(20) './QuantityPerUnit' ,
unitPrice money './UnitPrice' ,
unitsInStock smallint './UnitsInStock' ,
unitsOnOrder smallint './UnitsOnOrder' ,
reorderLevel smallint './ReorderLevel' ,
discontinued bit './Discontinued' ,
FakeText text './FakeText' ,
LsK_Lele
> Did you look at my sample?
Hi sloan,
yes, sorry, I saw it but forgot to reply.
I looked at your code, but I don't understand how I may use it for my
purpose: the thing I have to handle is manage anx xml that contain
something like a 2-levels table.
In my sample you can see that I have a list of products, and that's easy
to be managed in SQL/XML. But the problem is to retrieve the 2nd
"level": for each product (outer loop on the "products" table) there is
a list of components stored in the XML, and I have to retrieve this list
in a table that I'll use for looping (inner loop on the "components"
table) and save(check/update/and more operations) my data.
I'm sorry for my english, I hope I have correctly explained my problem.
Thank you!
sloan
The key is to create a second @variable table. But you put the "parent
product id" in that table as well.
Run the example. I've purposely put in a crappy naming convention of the
prefix "The" to avoid some ambuiguity.
You have to remember that xml is hierarchal, and tsql is relational.
And you have to wire up the code to make the conversion.
The "TheParentProductID" is the way I'm going this.... if I understand your
data model correctly.
But it looks like
Product
Component
are the 2 entities, and you have a M:N relationship between them.
I think the concept you're missing comes on this line
'./../../id'
where you can get the parent product id for each component.
Which again, is a "hierachal to relational" little bit of magic you gotta
pull.
Try this code:
/*
exec dbo.uspGoGoGo
'
<root>
<products>
<product>
<id>1</id>
<name>pc 1</name>
<components>
<component>
<id>111</id>
<quantity>2</quantity>
<description>This is a description (1)</description>
</component>
<component>
<id>222</id>
<quantity>2</quantity>
<description>This is a description(2)</description>
</component>
</components>
</product>
<product>
<id>2</id>
<name>pc 2</name>
<components>
<component>
<id>555</id>
<quantity>5</quantity>
<description>This is a description (5)</description>
</component>
<component>
<id>777</id>
<quantity>7</quantity>
<description>This is a description (7)</description>
</component>
</components>
</product>
</products>
</root>
'
, 0
*/
if exists (select * from sysobjects
where id = object_id('uspGoGoGo') and sysstat & 0xf = 4)
drop procedure uspGoGoGo
GO
CREATE PROCEDURE dbo.uspGoGoGo (
@xml_doc TEXT ,
@numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @hdoc INT -- handle to XML doc
DECLARE @errorTracker int -- used to "remember" the @@ERROR
DECLARE @updateRowCount int
DECLARE @insertRowCount int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @productupdate TABLE (
-- identityid int IDENTITY (1,1) ,
productId int ,
productName varchar(40) ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--The crappy "The" prefix naming convention on purpose here
DECLARE @componentupdate TABLE (
-- identityid int IDENTITY (1,1) ,
TheParentProductID int , -- <<<< DING DING DING -- This is the important
one.
TheComponentID int ,
TheQuantity int ,
[DescriptionOf] text ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @hdoc(with is the holder for
@xml_doc), and put it IN a variableTable
INSERT @productupdate
(
productId ,
productName ,
alreadyExists
)
SELECT
ProductID ,
ProductName ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@hdoc, '/root/products/product', 2) WITH (
productId int './id' , --<< Xml is case sensitive, so be careful
productName varchar(40) './name' , --<< Xml is case sensitive
alreadyExists bit
)
INSERT @componentupdate
(
TheParentProductID ,
TheComponentID ,
TheQuantity ,
DescriptionOf ,
alreadyExists
)
SELECT
ParentProductId ,
ComponentID ,
Quantity ,
DescriptionOf ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@hdoc, '/root/products/product/components/component', 2) WITH (
ParentProductId int './../../id' , -- <<<< DING DING DING --This is the
important one.
ComponentID int './id' , --<< Xml is case sensitive, so be careful
Quantity int './quantity' , --<< Xml is case sensitive
DescriptionOf text './description' ,
alreadyExists bit
)
--Remove the handle to the XML document, since we're done with
-- using the xmlDoc
EXEC sp_xml_removedocument @hdoc
--End XML usage
--lets differeniate between existing (update) and new ones (insert)
Update @productupdate
SET
alreadyExists = 1
FROM
@productupdate pu , dbo.Products p
WHERE
p.ProductID = pu.productId
--Uncomment the next lines based on your database.
/*
Update @componentupdate
SET
alreadyExists = 1
FROM
@componentupdate cu , dbo.Component c
WHERE
c.ComponentID = cu.TheComponentID
*/
--temp code here comment these lines out later
Print ' all records from @productupdate'
select * from @productupdate
print ''
--temp code here comment these lines out later
Print ' all records from @componentupdate'
select * from @componentupdate
print ''
/*
Ok, now you know the Products, and which ones already or don't already
exist.
You also know the Components, and which ones already or don't already exist.
AND
You know the relation between the Product and the Components...so you can
add records to the M:N table
(this is based on the "TheParentProductID"....
*/
GO
"LsK_Lele" <nos...@nospam.com> wrote in message
news:473c7d04$0$10622$4faf...@reader2.news.tin.it...
LsK_Lele
> Ok. I did the work on this one.
Hi sloan,
you're right, I messed up something on your previous posts.
I'll edit your code (it seems to work very very good) and use it, thank
you very much!
Bye