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
 union 2 sheme xml file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nord
Posting Yak Master

122 Posts

Posted - 10/21/2013 :  21:34:45  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 10/22/2013 :  08:18:36  Show Profile  Reply with Quote
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

122 Posts

Posted - 10/22/2013 :  09:08:30  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 10/22/2013 :  09:13:03  Show Profile  Reply with Quote

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')



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

nord
Posting Yak Master

122 Posts

Posted - 10/22/2013 :  09:19:56  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 10/22/2013 :  10:10:11  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.1 seconds. Powered By: Snitz Forums 2000