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