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
 union 2 sheme xml file

Author  Topic 

nord
Posting Yak Master

126 Posts

Posted - 2013-10-21 : 21:34:45
Hi,
I need generate un xml file ,structure:



<?xml version="1.0" encoding="UTF-8" ?>
- <EmployeeImportT xmlns="http://epicor.com/EmployeeImport.xsd">
- <Employee ActionType="R" EmployeeNumber="202940" EmployeeType="A">
<FirstName>Marie-Pier</FirstName>
<LastName>Emond</LastName>
<ActiveStatus>Y</ActiveStatus>
<StatusCode>HIRE</StatusCode>
<DateHired>2013-01-01</DateHired>
<UserName>MEmond</UserName>
<DomainName>GYEP</DomainName>
<PrimaryPositionCode>777</PrimaryPositionCode>
<PrimaryLocationNumber>0117</PrimaryLocationNumber>
<DepartmentNumber>9999</DepartmentNumber>
<ISPSecurityLevel>10</ISPSecurityLevel>
<POSSecurityLevel>10</POSSecurityLevel>
<Password>Yellow13</Password>
<Accountability>EITHER</Accountability>
</Employee>
</EmployeeImportT>


this is my sp,if i generate one querry its wok perfect ,second querry too,but how i can make on xml file with 2 shema together?


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(

SELECT

[ActionType] AS ActionType

,[EmployeeNumber] AS EmployeeNumber

,[EmployeeType] AS EmployeeType

FROM

Employee

FOR XML auto)

,



(

SELECT

[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

Employee



FOR XML AUTO, ELEMENTS)



from Employee

FOR XML PATH('')

end

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 08:18:36
why you need two queries? wont this be enough?


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

Employee

FOR XML PATH('')




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nord
Posting Yak Master

126 Posts

Posted - 2013-10-22 : 09:08:30
its very interesting,dont thing so ,but give me error:
Msg 6864, Level 16, State 1, Line 1
Row tag omission (empty row tag name) cannot be used with attribute-centric FOR XML serialization.
thanks a lot for you help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 09:13:03
[code]
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

Employee

FOR XML PATH('Employee')
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nord
Posting Yak Master

126 Posts

Posted - 2013-10-22 : 09:19:56
WOW,Its wonderful,no words!
so thank you ,you are best!!!!!!!!!!!!!!!!!!!!!!!!!!!
THANKS A LOT FOR YOUR HELP!!!!!!!!!!!!!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 10:10:11
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -