February 25, 2014 at 1:52 am
Hi all,
I want to create a single xml file on basis of a tag.
please find the script below to create table and insert dummy data:
CREATE TABLE [dbo].[CreateXML](
[CreateXMLID] [int] IDENTITY(1,1) NOT NULL,
[InstanceId] [nchar](3) NOT NULL,
[SiteID] [nchar](3) NOT NULL,
[Locale] [nvarchar](20) NOT NULL,
[Key] [nvarchar](400) NOT NULL,
[Value] [nvarchar](400) NOT NULL,
[StatusFlag] [bit] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[AddedBy] [nvarchar](75) NOT NULL,
[DateChanged] [datetime] NULL,
[ChangedBy] [nvarchar](75) NULL,
[LocalDateChanged] [datetime] NOT NULL,
[LocalChangedBy] [nvarchar](75) NOT NULL,
[ProcessedFlag] [bit] NOT NULL,
[ProcessedDate] [datetime] NULL,
[ReturnCode] [nchar](6) NULL,
[EmailSentFlag] [bit] NOT NULL,
CONSTRAINT [PK_CreateXML] PRIMARY KEY CLUSTERED
(
[CreateXMLID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[CreateXML] ON
INSERT [dbo].[CreateXML] ([CreateXMLID], [InstanceId], [SiteID], [Locale], [Key], [Value], [StatusFlag], [DateAdded], [AddedBy], [DateChanged], [ChangedBy], [LocalDateChanged], [LocalChangedBy], [ProcessedFlag], [ProcessedDate], [ReturnCode], [EmailSentFlag]) VALUES (2, N'001', N'001', N'en_US', N'btnAdd', N'Add', 0, CAST(0x0000A2A800000000 AS DateTime), N'kk', NULL, NULL, CAST(0x0000A2A800000000 AS DateTime), N'kk', 0, CAST(0x0000A2A800000000 AS DateTime), NULL, 0)
INSERT [dbo].[CreateXML] ([CreateXMLID], [InstanceId], [SiteID], [Locale], [Key], [Value], [StatusFlag], [DateAdded], [AddedBy], [DateChanged], [ChangedBy], [LocalDateChanged], [LocalChangedBy], [ProcessedFlag], [ProcessedDate], [ReturnCode], [EmailSentFlag]) VALUES (3, N'001', N'001', N'en_US', N'btnCancel', N'Cancel', 0, CAST(0x0000A2A800000000 AS DateTime), N'kk', NULL, NULL, CAST(0x0000A2A800000000 AS DateTime), N'kk', 0, CAST(0x0000A2A800000000 AS DateTime), NULL, 0)
INSERT [dbo].[CreateXML] ([CreateXMLID], [InstanceId], [SiteID], [Locale], [Key], [Value], [StatusFlag], [DateAdded], [AddedBy], [DateChanged], [ChangedBy], [LocalDateChanged], [LocalChangedBy], [ProcessedFlag], [ProcessedDate], [ReturnCode], [EmailSentFlag]) VALUES (1003, N'001', N'001', N'It_IT', N'btnAdd', N'Aggiungi', 0, CAST(0x0000A2AB00000000 AS DateTime), N'kk', NULL, NULL, CAST(0x0000A2AB00000000 AS DateTime), N'kk', 0, CAST(0x0000A2AB00000000 AS DateTime), NULL, 0)
INSERT [dbo].[CreateXML] ([CreateXMLID], [InstanceId], [SiteID], [Locale], [Key], [Value], [StatusFlag], [DateAdded], [AddedBy], [DateChanged], [ChangedBy], [LocalDateChanged], [LocalChangedBy], [ProcessedFlag], [ProcessedDate], [ReturnCode], [EmailSentFlag]) VALUES (1004, N'001', N'002', N'It_IT', N'btnCancel', N'anullare', 0, CAST(0x0000A2AB00000000 AS DateTime), N'kk', NULL, NULL, CAST(0x0000A2AB00000000 AS DateTime), N'kk', 0, CAST(0x0000A2AB00000000 AS DateTime), NULL, 0)
SET IDENTITY_INSERT [dbo].[CreateXML] OFF
ALTER TABLE [dbo].[CreateXML] ADD CONSTRAINT [DF_CreateXML_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[CreateXML] ADD CONSTRAINT [DF_CreateXML_ProcessedFlag] DEFAULT ((0)) FOR [ProcessedFlag]
GO
ALTER TABLE [dbo].[CreateXML] ADD CONSTRAINT [DF_CreateXML_EmailSentFlag] DEFAULT ((0)) FOR [EmailSentFlag]
GO
Below is the output I required:
<International>
<ContentRecords locale = "en_US">
<ContentRecord>
<ContentId>btnAdd</ContentId>
<Content>Add</Content>
</ContentRecord>
<ContentRecord>
<ContentId>btnCancel</ContentId>
<Content>Cancel</Content>
</ContentRecord>
</ContentRecords>
<ContentRecords locale = "it_IT">
<ContentRecord>
<ContentId>btnAdd</ContentId>
<Content>Aggiungi</Content>
</ContentRecord>
<ContentRecord>
<ContentId>btnCancel</ContentId>
<Content>anullare</Content>
</ContentRecord>
</ContentRecords>
</International>
This is what I have tried but not getting expected output
SELECT [Key] as 'ContentId',Value as 'Content'
FROM CreateXML WITH(NOLOCK)
FOR XML RAW('ContentRecord'), ROOT('ContentRecordslocale'), Elements
Please help
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 25, 2014 at 2:46 am
Like this?
SELECT
O.[Locale] [@locale],
(SELECT
I.[Key] as 'ContentId',
I.[Value] as 'Content'
FROM
CreateXML I
WHERE
I.[Locale] = O.[Locale]
FOR XML PATH('ContentRecord'), TYPE
)
FROM
(
SELECT DISTINCT
[Locale]
FROM
CreateXML
) O
FOR XML PATH('ContentRecords'), ROOT('International'), TYPE
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply