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

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

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

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

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!!!!!!!!!!!!!!!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources