Products - Batch Operations
Batch Create, Update and Delete WooCommerce Products using SQL Server
- See SQLHTTP easy setup for WooCommerce
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
CREATE PROCEDURE usp_WooCommerce_v2_Products_Batch_Update( @Profile nvarchar(100), @Body varchar(MAX), @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @URL nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier SET @URL = SQLHTTP.net.AuthParam(@Profile, 'RootURL') EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'wp-json', 'wc', 'v2', 'products', 'batch' EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_WooCommerce_v2_Auth_Header @HTTPSessionID, @Profile, @URL, 'POST' EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'POST', @Body = @Body, @ContentType = 'application/json', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @Body varchar(MAX) SET @Body = '{ "create": [ { "name": "Woo Single #1", "type": "simple", "regular_price": "21.99", "virtual": true, "downloadable": true, "downloads": [ { "name": "Woo Single", "file": "http://demo.woothemes.com/woocommerce/wp-content/uploads/sites/56/2013/06/cd_4_angle.jpg" } ], "categories": [ { "id": 34 } ], "images": [ { "src": "http://demo.woothemes.com/woocommerce/wp-content/uploads/sites/56/2013/06/cd_4_angle.jpg", "position": 0 } ] }, { "name": "New Premium Quality", "type": "simple", "regular_price": "29.99", "description": "Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Vestibulum tortor quam, feugiat vitae, ultricies eget, tempor sit amet, ante. Donec eu libero sit amet quam egestas semper. Aenean ultricies mi vitae est. Mauris placerat eleifend leo.", "short_description": "Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas.", "categories": [ { "id": 18 }, { "id": 20 } ], "images": [ { "src": "http://demo.woothemes.com/woocommerce/wp-content/uploads/sites/56/2013/06/T_2_front.jpg", "position": 0 }, { "src": "http://demo.woothemes.com/woocommerce/wp-content/uploads/sites/56/2013/06/T_2_back.jpg", "position": 1 } ] } ], "update": [ { "id": 104, "default_attributes": [ { "id": 6, "name": "Color", "option": "Green" }, { "id": 0, "name": "Size", "option": "M" } ] } ], "delete": [ 108 ] }' EXEC usp_WooCommerce_v2_Products_Batch_Update @Profile = 'My WooCommerce', @Body = @Body, @Response = @Response OUTPUT, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @X xml SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0') --The XPath syntax below was easily generated by executing the following commands: --EXEC SQLHTTP.net.XQueryHelper @X --and then executing: --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/create' -- --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/delete' -- --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/update' -- SELECT 'create' AS [type] ,T.C.value(N'JsonObject[1]/@id', N'nvarchar(MAX)') AS [id] ,T.C.value(N'JsonObject[1]/@name', N'nvarchar(MAX)') AS [name] ,T.C.value(N'JsonObject[1]/error[1]/@message', N'nvarchar(MAX)') AS [message] FROM @X.nodes(N'/JsonObject/create') T(C) UNION SELECT 'update' AS [type] ,T.C.value(N'JsonObject[1]/@id', N'nvarchar(MAX)') AS [id] ,T.C.value(N'JsonObject[1]/@name', N'nvarchar(MAX)') AS [name] ,T.C.value(N'JsonObject[1]/error[1]/@message', N'nvarchar(MAX)') AS [message] FROM @X.nodes(N'/JsonObject/update') T(C) UNION SELECT 'delete' AS [type] ,T.C.value(N'JsonObject[1]/@id', N'nvarchar(MAX)') AS [id] ,T.C.value(N'JsonObject[1]/@name', N'nvarchar(MAX)') AS [name] ,T.C.value(N'JsonObject[1]/error[1]/@message', N'nvarchar(MAX)') AS [message] FROM @X.nodes(N'/JsonObject/delete') T(C) END |
|
1 2 3 4 5 6 7 8 |
type id name message -------- -------- ---------------------------- -------------- create 110 Woo Single #1 NULL create 113 New Premium Quality NULL delete 108 Ship Your Idea NULL update 104 Premium Quality T-shirts NULL |
- Attribute – Create
- Attribute – Delete
- Attribute – Fetch
- Attribute – Update
- Attribute Term – Create
- Attribute Term – Delete
- Attribute Term – Fetch
- Attribute Term – Update
- Attribute Terms – Batch Operations
- Attribute Terms – Fetch
- Attributes – Batch Operations
- Attributes – Fetch
- Categories – Batch Operations
- Categories – Fetch
- Category – Create
- Category – Delete
- Category – Fetch
- Category – Update
- Coupon – Create
- Coupon – Delete
- Coupon – Fetch
- Coupon – Update
- Coupons – Batch Operations
- Coupons – Fetch
- Customer – Create
- Customer – Delete
- Customer – Fetch
- Customer – Update
- Customer Downloads – Fetch
- Customers – Batch Operations
- Customers – Fetch
- Order – Create
- Order – Delete
- Order – Fetch
- Order – Update
- Order Note – Create
- Order Note – Delete
- Order Note – Fetch
- Order Notes – Fetch
- Orders – Batch Operations
- Orders – Fetch
- Payment Gateway – Fetch
- Payment Gateway – Update
- Payment Gateways – Fetch
- Product – Create
- Product – Delete
- Product – Fetch
- Product – Update
- Products – Batch Operations
- Products – Fetch
- Refund – Create
- Refund – Delete
- Refund – Fetch
- Refunds – Fetch
- Reports – Fetch
- Review – Fetch
- Reviews – Fetch
- Sales Report – Fetch
- Setting Option – Fetch
- Setting Option – Update
- Setting Options – Batch Operations
- Setting Options – Fetch
- Settings Groups – Fetch
- Shipping Class – Create
- Shipping Class – Delete
- Shipping Class – Fetch
- Shipping Class – Update
- Shipping Classes – Batch Oprations
- Shipping Classes – Fetch
- Shipping Method – Fetch
- Shipping Methods – Fetch
- Shipping Zone – Create
- Shipping Zone – Delete
- Shipping Zone – Fetch
- Shipping Zone – Update
- Shipping Zone Method – Create
- Shipping Zone Method – Delete
- Shipping Zone Method – Fetch
- Shipping Zone Method – Update
- Shipping Zone Methods – Fetch
- Shipping Zones – Fetch
- System Status Tool – Fetch
- System Status Tool – Run
- System Status Tools – Fetch
- Tag – Create
- Tag – Delete
- Tag – Fetch
- Tag – Update
- Tags – Batch Operations
- Tags – Fetch
- Tax Class – Create
- Tax Class – Delete
- Tax Classes – Fetch
- Tax Rate – Create
- Tax Rate – Delete
- Tax Rate – Fetch
- Tax Rate – Update
- Tax Rates – Batch Operations
- Tax Rates – Fetch
- Top Sellers Report – Fetch
- Variation – Create
- Variation – Delete
- Variation – Fetch
- Variation – Update
- Variations – Batch Operations
- Variations – Fetch
- Webhook – Create
- Webhook – Delete
- Webhook – Fetch
- Webhooks – Batch Operations
- Webhooks – Fetch
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.