Jacob L
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.sq l" -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.
<?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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.