Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nord
Posting Yak Master

126 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
52326 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

126 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
52326 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

126 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
52326 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  
 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.12 seconds. Powered By: Snitz Forums 2000