FOR XML with xp_commandshell - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

FOR XML with xp_commandshell

Author

Topic

evansl
Starting Member

6 Posts

Posted - 2010-11-02 : 07:43:51

I have been tasked with creating an xml from a trigger for the use of a web site via Fredhopper. The trigger is to remove products from sale as soon as they go out of stock. I have created a simple select for testing but can't seem to generate the xml using xp_cmdshell - I get an output with hints on. Please see the attached code. (Testing on a simple select - Not FOR XML works fine)

DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)

SET @FileName = 'c:\Test.xml'

SET @bcpCommand = ' bcp "SELECT 
(
SELECT TOP 2 ''update'' AS "@operation", pc.ProdColourID AS "@identifier", 
(
SELECT TOP 2 ''int'' AS "@type", ''instock'' AS "@identifier",
0 AS value
FOR XML PATH (''attribute''), TYPE
)
FROM Inventory2.dbo.Item AS i WITH (NOLOCK)
	INNER JOIN DRLNewProducts.dbo.tbl_Product_Colours AS pc WITH (NOLOCK)
		ON pc.Product_Code = i.SKU 
FOR XML PATH (''item''), TYPE
) 
FOR XML PATH (''), ROOT (''items'')" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -c -T -x'

EXEC master..xp_cmdshell @bcpCommand

Can anyone help?

Leon Evans

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-02 : 11:05:35

we could help but sample data of Item and tbl_product_colours would be helpful as such


DECLARE @Item table(field1 int, field2 nvarchar(120))
DECLARE @Product_colors(colorID nvarchar(23), field2 nvarchar(120))
insert into @item
select 23, 'whodat'
UNION ALL
select 3, 'Giants'

etc

If you don't have the passion to help people, you have no passion

evansl
Starting Member

6 Posts

Posted - 2010-11-02 : 11:31:51

Below is the test data

DECLARE @Item TABLE
(
SKU VARCHAR(50)
)

DECLARE @Product_Colours TABLE
(
ProdColourID INT,
Product_Code VARCHAR(50)
)

INSERT INTO @Item
(
SKU
) VALUES ('ProdCode123')

INSERT INTO @Product_Colours
(
ProdColourID,
Product_Code
) VALUES (11246,'ProdCode123')

SELECT
(
SELECT TOP 2 'update' AS "@operation", pc.ProdColourID AS "@identifier",
(
SELECT TOP 2 'int' AS "@type", 'instock' AS "@identifier",
0 AS value
FOR XML PATH ('attribute'), TYPE
)
FROM @Item AS i
INNER JOIN @Product_Colours AS pc
ON pc.Product_Code = i.SKU
FOR XML PATH ('item'), TYPE
)
FOR XML PATH (''), ROOT ('items')

GO

And here is what I am trying to acheive

DECLARE @Item TABLE
(
SKU VARCHAR(50)
)

DECLARE @Product_Colours TABLE
(
ProdColourID INT,
Product_Code VARCHAR(50)
)

INSERT INTO @Item
(
SKU
) VALUES ('ProdCode123')

INSERT INTO @Product_Colours
(
ProdColourID,
Product_Code
) VALUES (11246,'ProdCode123')

DECLARE @FileName varchar(50),
@bcpCommand varchar(5000)

SET @FileName = 'c:\Test.xml'

SET @bcpCommand = ' bcp "SELECT
(
SELECT TOP 2 ''update'' AS "@operation", pc.ProdColourID AS "@identifier",
(
SELECT TOP 2 ''int'' AS "@type", ''instock'' AS "@identifier",
0 AS value
FOR XML PATH (''attribute''), TYPE
)
FROM @Item AS i
INNER JOIN @Product_Colours AS pc
ON pc.Product_Code = i.SKU
FOR XML PATH (''item''), TYPE
)
FOR XML PATH (''''), ROOT (''items'')" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -c -T -x -SKRISHNAK'

--PRINT @bcpCommand

EXEC master..xp_cmdshell @bcpCommand

evansl
Starting Member

6 Posts

Posted - 2010-11-02 : 11:46:01

Also worked for me - However when the FOR XML query is in the bcp command it doesn't work - Below

DECLARE @Item TABLE
(
SKU VARCHAR(50)
)

DECLARE @Product_Colours TABLE
(
ProdColourID INT,
Product_Code VARCHAR(50)
)

INSERT INTO @Item
(
SKU
) VALUES ('ProdCode123')

INSERT INTO @Product_Colours
(
ProdColourID,
Product_Code
) VALUES (11246,'ProdCode123')

DECLARE @FileName varchar(50),
@bcpCommand varchar(5000)

SET @FileName = 'c:\Test.xml'

SET @bcpCommand = ' bcp "SELECT
(
SELECT TOP 2 ''update'' AS "@operation", pc.ProdColourID AS "@identifier",
(
SELECT TOP 2 ''int'' AS "@type", ''instock'' AS "@identifier",
0 AS value
FOR XML PATH (''attribute''), TYPE
)
FROM @Item AS i
INNER JOIN @Product_Colours AS pc
ON pc.Product_Code = i.SKU
FOR XML PATH (''item''), TYPE
)
FOR XML PATH (''''), ROOT (''items'')" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -c -T -x'

--PRINT @bcpCommand

EXEC master..xp_cmdshell @bcpCommand

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-02 : 12:00:31

worked for me

DECLARE @Item table(ItemDescr nvarchar(120), SKU nvarchar(120))
DECLARE @Product_colors table(ProdColourID nvarchar(10), Product_Code nvarchar(120))

insert into @item
select 'Chicken Wings', '3345SKU'
UNION ALL
select 'Chicken Nuggets', '3346SKU'
UNION ALL
select 'Widgets', '3347SKU'

--Yummy! look at them blue chickens nuggets
INSERT INTO @Product_colors
select '#0000FF', '3345SKU'
UNION ALL
select '#00FFFF', '3346SKU'
UNION ALL
select '#C0C0C0', '3347SKU'

DECLARE @output XML
SET @output = (
SELECT
(

SELECT TOP 2 'update' AS "@operation",
pc.ProdColourID AS "@identifier",
(
SELECT TOP 2 'int' AS "@type",
'instock' AS "@identifier",
0 AS value
FOR XML PATH ('attribute'), TYPE
)
FROM @item AS i
INNER JOIN @Product_colors AS pc
ON pc.Product_Code = i.SKU
FOR XML PATH ('item'), TYPE
)
FOR XML PATH (''), ROOT ('items')
)
CREATE TABLE bulky(xmlish XML)

INSERT INTO bulky
SELECT @output

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = 'c:\Test.xml'

SET @bcpCommand = ' bcp "SELECT xmlish from bulky" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -c -T -x'

PRINT @bcpCommand

EXEC master..xp_cmdshell @bcpCommand

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-02 : 12:05:11

"However when the FOR XML query is in the bcp command it doesn't work" therefore remove FOR XML query in the bcp command and use what is laid out for you
1. create xml variable
2. create table with xml field
3. dump FOR XML value into xml variable
4. INSERT INTO table with xml field value of xml variable
5. Bcp to xml from table with xml field

If you don't have the passion to help people, you have no passion

evansl
Starting Member

6 Posts

Posted - 2010-11-02 : 12:11:07

Thanks for that, however I have been told that the xml needs to be provided in the exact format below for the application to process. I can't pass it in as a string. Maybe I need to explore other options?

<items>
<item operation="update" identifier="8275">
<attribute type="int" identifier="instock">
<value>0</value>
</attribute>
</item>
<item operation="update" identifier="8275">
<attribute type="int" identifier="instock">
<value>0</value>
</attribute>
</item>
</items>

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-02 : 12:15:36

xml is string. did it dump the xml file for you? how did you open it that causes you the concern? open it using internet explorer, what do you see

If you don't have the passion to help people, you have no passion

evansl
Starting Member

6 Posts

Posted - 2010-11-02 : 12:18:07

Opening with notepad caused concern - looking very good in Internet Explorer :) Thank you very much

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-02 : 12:23:21

just curious, what is the application that consumes this xml. why create xml file to a physical location, why not call a stored procedure /view from application and consume the returned xml data.

Go Arsenal!

If you don't have the passion to help people, you have no passion

evansl
Starting Member

6 Posts

Posted - 2010-11-02 : 12:43:09

The application is Fredhopper. This hosts the data for all our web sites and is a very powerful tool for site navigation and search. When a product becomes out of stock in the warehouse we need to immediatly remove it from sale (new next day delivery appliance site currently in development) which will be done via trigger. All data is loaded into Fredhopper using XMLs

http://www.fredhopper.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-02 : 16:13:41

can you create a webservice or rest based api in your internal network, expose it so that it is called from fredhopper. no manual copying of xml etc. dynamic

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources