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

Jacob Lepley
Jacob Lepley used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jacob LepleyBusiness Systems Programming Analyst

Author

Commented:
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.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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

Business Systems Programming Analyst
Commented:
Sorry I went a different route while i was waiting. I appreciate the response, but this will get too complex to explain and was just easier to do in c#.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial