Problem with XML file - 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

Other SQL Server 2008 Topics

Problem with XML file

Author

Topic

nord
Posting Yak Master

126 Posts

Posted - 2013-10-21 : 13:21:10

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-21 : 13:27:37

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?

nord
Posting Yak Master

126 Posts

Posted - 2013-10-21 : 13:57:23

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-21 : 14:02:27

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.

nord
Posting Yak Master

126 Posts

Posted - 2013-10-21 : 14:11:46

when I run SP result is:XML_F52E2B61_18A1_11d1_B105_00805F49916B
how I can see (open) file?
thanks

nord
Posting Yak Master

126 Posts

Posted - 2013-10-21 : 14:26:30

sory this is not result this is name of column

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-21 : 14:31:27

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)

nord
Posting Yak Master

126 Posts

Posted - 2013-10-21 : 14:50:26

give ne "null"

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-21 : 15:41:04

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources