Link to home
Start Free TrialLog in
Avatar of Jacob L
Jacob LFlag for United States of America

asked on

using sqlcmd and XML ON to output sql into an XML file.

I have a sql script that writes the selection into xml format. I am trying to write a batch file that will output this data into an xml file. Using :XML ON I am receiving the following error in the output file:

<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>
HResult 0x80004005, Level 16, State 1
No description provided

my .bat file code:
sqlcmd -i "ProductHierarchy-Dept-Cat-Subcat.sql" -S "SC-L-WINSQL-001" -d "HQ_L" -o "output.xml" -y0

If I turn :XML OFF it prints out the entire data selection into one line.

SET NOCOUNT ON 
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON

--drop tables if exist
If(OBJECT_ID('tempdb..#importType') Is Not Null)
Begin
    Drop Table #importType
End

--select data
If(OBJECT_ID('tempdb..#AMSprocuctHierarchy') Is Not Null)
Begin
    Drop Table #AMSprocuctHierarchy
End

select 
	distinct DIM.pos_dept as Department, 
	SD.DESCRIPTION as DeptDesc,
	--left(DIM.category,4) as Category, 
	--right(rtrim(DIM.category),4) as Subcategory,  
	DIM.upc as UPC, DIM.description as ItemDesc, 
	substring(DIM.CATEGORY, 1, 4) as Category, 
	substring(DIM.CATEGORY, 5, 8) as SubCategory, 
	CAT.DESCRIPTION as CategoryDesc,
	subcat.DESCRIPTION as SubcatDesc
		into #AMSprocuctHierarchy
			from DHAT_ITEM_MASTER DIM
			join store_department SD on DIM.pos_dept = SD.STORE_POS_DEPARTMENT
			join CATEGORY subcat
			   on subcat.CATEGORY = DIM.CATEGORY
			join CATEGORY cat
			   on cat.CATEGORY = substring(DIM.CATEGORY, 1, 4) + '0000    '
			   order by Dim.pos_dept

--Create file types table
create table #importType
	(
		Type Varchar(100)
	)
insert into #importType
	(Type)
	VALUES('Department'),('Brand'),('Vendor'),('Location')
--select * from #importType

-----------------------------------------------------------------------------------------------------------------------
--xml output

declare @xml xml 

set @xml = (Select  
--remove ProductHierarchy
	(select Type as HierarchyID from #importType
		where type = 'Department' 
			for xml raw ('RemoveHierarchy'), ELEMENTS, type
	),

--add ProductHierarchy
	(select Type as HierarchyID, 'Dept' as LeafName from #importType
		where type = 'Department' 
			for xml raw ('AddHierarchy'), ELEMENTS, type
	),

--Add Deparment hierarchy
	(Select 
		distinct 'Department' as HierarchyID,  
		rtrim(Department) as Branch, 
		rtrim(DeptDesc) as LeafName,
		rtrim(DeptDesc) as DisplayID
		from #AMSprocuctHierarchy
				for xml raw ('AddBranch'), ELEMENTS,type
	),	
--Add Categories
	(
	Select 
		distinct 'Department' as HierarchyID,  
		rtrim(Department) +','+ Category as Branch,
		rtrim(CategoryDesc) as LeafName,
		rtrim((Category) + ' - ' + (CategoryDesc)) as DisplayID
		from #AMSprocuctHierarchy
				for xml raw ('AddBranch'), ELEMENTS, type
	),
--Add Sub-Categories
	(
	Select 
	distinct	'Department' as HierarchyID,  
		rtrim(Department) +','+ Category +','+ rtrim(SubCategory) as Branch,
		rtrim(SubcatDesc) as LeafName,
		rtrim(SubCategory) + ' - ' + rtrim(SubcatDesc) as DisplayID
		from #AMSprocuctHierarchy
				for xml raw ('AddBranch'),ELEMENTS,Type
	) 
	for xml raw ('ProductHierarchy'), ELEMENTS XSINIL,Type
	)


:XML ON
select @xml 

SET NOCOUNT OFF

Open in new window

Avatar of Jacob L
Jacob L
Flag of United States of America image

ASKER

Well I didn't expect it to take this long to get some help.
I am open for some suggestions as my main goal here is automate the process to output my sql query into XML. Keep in mind the query returns quite a bit of information. So the approach I am taking may not be the best approach. Feel free to share another option if you have one.
Avatar of Raja Jegan R
Sorry, missed your question earlier and hence the delay..
Kindly let me know whether the xml file you are generating is too big or only one value..
If one value, then try inserting that xml value into a global temp table(like ##temp) and then use SQLCMD or BCP command to export that record to an xml file directly
bcp "SELECT ur_XML_col FROM ##temp" queryout "c:\ur_xml.xml" -S urServerName -d urdbname -T -w -q

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jacob L
Jacob L
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial