SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Problem with XML file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nord
Posting Yak Master

126 Posts

Posted - 10/21/2013 :  13:21:10  Show Profile  Reply with Quote
Hi ,
I'm new with XML file ,I need create Xml file from querry:
SELECT
[ActionType] AS ActionType
,[EmployeeNumber] AS EmployeeNumber
,[EmployeeType] AS EmployeeType
,[FirstName] AS FirstName
,[LastName] AS LastName
,[ActiveStatus] AS ActiveStatus
,[StatusCode] AS StatusCode
,[DateHired] AS DateHired
,[UserName] AS UserName
,[DomainName] AS DomainName
,[PrimaryPositionCode] AS PrimaryPositionCode
,[PrimaryLocationNumber] AS PrimaryLocationNumber
,[DepartmentNumber] AS DepartmentNumber
,[ISPSecurityLevel] AS ISPSecurityLevel
,[POSSecurityLevel] AS POSSecurityLevel
,[Password] AS [Password]
,[Accountability] AS Accountability


FROM
[Yellow_Epicor].[dbo].Basic_Employee_Data
FOR
XML PATH('Employee ActionType="R" EmployeeNumber="202940" EmployeeType="A"')

but error is:
Msg 6850, Level 16, State 1, Procedure XML_Liste employés, Line 12
Row name 'Employee ActionType="R" EmployeeNumber="202940" EmployeeType="A"' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.

I searched in google but didn't find some answer...
Thanks

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 10/21/2013 :  13:27:37  Show Profile  Reply with Quote
Are you trying to use a WHERE clause (filtering condition) with what you have in the PATH? If so, don't do that. Try this:
....
FROM
[Yellow_Epicor].[dbo].Basic_Employee_Data
WHERE [Employee ActionType]='R' AND EmployeeNumber='202940' AND EmployeeType='A'
FOR
XML PATH('EmployeeData')
If that doesn't do it for you, can you post a sample of the resulting XML you are trying to create?
Go to Top of Page

nord
Posting Yak Master

126 Posts

Posted - 10/21/2013 :  13:57:23  Show Profile  Reply with Quote
Hi,I tried like that:
USE [Yellow_Epicor]
GO
/****** Object: StoredProcedure [dbo].[XML_Liste employés] Script Date: 10/21/2013 09:43:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Evgeny,Chepeleutser
-- Create date: 2013-10-08
-- Description: Create XML file
-- =============================================
ALTER PROCEDURE [dbo].[XML_Liste employés]

AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
SELECT
[ActionType] AS ActionType
,[EmployeeNumber] AS EmployeeNumber
,[EmployeeType] AS EmployeeType
,[FirstName] AS FirstName
,[LastName] AS LastName
,[ActiveStatus] AS ActiveStatus
,[StatusCode] AS StatusCode
,[DateHired] AS DateHired
,[UserName] AS UserName
,[DomainName] AS DomainName
,[PrimaryPositionCode] AS PrimaryPositionCode
,[PrimaryLocationNumber] AS PrimaryLocationNumber
,[DepartmentNumber] AS DepartmentNumber
,[ISPSecurityLevel] AS ISPSecurityLevel
,[POSSecurityLevel] AS POSSecurityLevel
,[Password] AS [Password]
,[Accountability] AS Accountability

FROM
[Yellow_Epicor].[dbo].Basic_Employee_Data
WHERE [ActionType]='R' AND EmployeeNumber='202940' AND EmployeeType='A'
FOR
XML PATH('EmployeeData')

end
querry write me result,but i can't open this
thanks
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 10/21/2013 :  14:02:27  Show Profile  Reply with Quote
Didn't quite follow what you meant when you said "you can't open it". When you run the stored procedure from SSRS, ( EXEC [dbo].[XML_Liste employés] ) it should show you one line which looks like a hyperlink. When you click on that link, the XML should open in a new window.
Go to Top of Page

nord
Posting Yak Master

126 Posts

Posted - 10/21/2013 :  14:11:46  Show Profile  Reply with Quote
when I run SP result is:XML_F52E2B61_18A1_11d1_B105_00805F49916B
how I can see (open) file?
thanks
Go to Top of Page

nord
Posting Yak Master

126 Posts

Posted - 10/21/2013 :  14:26:30  Show Profile  Reply with Quote
sory this is not result this is name of column
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 10/21/2013 :  14:31:27  Show Profile  Reply with Quote
Change your select as shown in red below
SELECT * FROM(
SELECT 
[ActionType] AS ActionType
,[EmployeeNumber] AS EmployeeNumber
,[EmployeeType] AS EmployeeType
,[FirstName] AS FirstName
,[LastName] AS LastName
,[ActiveStatus] AS ActiveStatus
,[StatusCode] AS StatusCode
,[DateHired] AS DateHired
,[UserName] AS UserName
,[DomainName] AS DomainName
,[PrimaryPositionCode] AS PrimaryPositionCode
,[PrimaryLocationNumber] AS PrimaryLocationNumber
,[DepartmentNumber] AS DepartmentNumber
,[ISPSecurityLevel] AS ISPSecurityLevel
,[POSSecurityLevel] AS POSSecurityLevel
,[Password] AS [Password]
,[Accountability] AS Accountability

FROM
[Yellow_Epicor].[dbo].Basic_Employee_Data
WHERE [ActionType]='R' AND EmployeeNumber='202940' AND EmployeeType='A'
FOR
XML PATH('EmployeeData')) s(YourColumnNameHere)
If you want the result as XML rather than string (which this will return), change the last line to
XML PATH('EmployeeData'), TYPE) s(YourColumnNameHere)
Go to Top of Page

nord
Posting Yak Master

126 Posts

Posted - 10/21/2013 :  14:50:26  Show Profile  Reply with Quote
give ne "null"
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 10/21/2013 :  15:41:04  Show Profile  Reply with Quote
quote:
Originally posted by nord

give ne "null"

Copy and run this example - it should give you one column with the name Employees. Then compare to your code and see what you are doing differently.
CREATE TABLE #tmp (EmployeeNumber VARCHAR(32));
INSERT INTO #tmp VALUES ('1234'),('5678');

SELECT * FROM (
SELECT EmployeeNumber FROM #tmp FOR XML PATH('Employees'),TYPE) s(Employees);

DROP TABLE #tmp;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000