EDN Admin
Well-known member
Im trying to create an XML file with the following header info:
<span><?<span>xml version="1.0" encoding="UTF-8" <span>?>
<a rel="nofollow - <span><!-- <span>
<pre> This Document was last updated on 20-July-2011 14:41 IST [/code]
<span> <span>-->
<div style="text-indent:-2em http://acff-documentation.s3.amazonaws.com/ACFF_XML_Sample.xml# -
<span><<span>mediaFeed<span> xmlns:xsi<span>=" http://www.w3.org/2001/XMLSchema-instance <span>"<span> xsi:noNamespaceSchemaLocation<span>=" AMF-XML-Schema.xsd <span>"<span>>
from the following piece of code. I just need the header section correct, at the moment the following XML tags are attached to each ROOT defined in the XML i.e.
<div style="text-indent:-2em <span><span> xmlns:xsi<span>=" http://www.w3.org/2001/XMLSchema-instance <span>"<span>>
<div style="text-indent:-2em <span><span>I If any one can help I would be most appreciated.
<div style="text-indent:-2em <span><span> Thanks
<div style="text-indent:-2em <span><br/>
<div style="text-indent:-2em
<div style="text-indent:-2em <span>
<pre>SELECT (
SELECT AMF-XML-Schema.xsd AS noNamespaceSchemaLocation,
(SELECT
(SELECT ICPN AS EAN
FROM tbConfiguration cx1
WHERE cx1.ConfigurationId = c.ConfigurationId
FOR XML Path(itemIdentifier), TYPE),
(SELECT ConfigurationTitle AS Title
FROM tbConfiguration cx2
WHERE cx2.ConfigurationId = c.ConfigurationId
FOR XML Path(OriginalLanguage), ROOT(title), Type),
ISNULL(afm.AmazonFormatName, cf.FormatName) AS productForm,
(SELECT ISNULL(arm.AmazonRoleName, cr3.RoleName) AS Role,
cn3.Name AS Name
FROM tbConfiguration cx3
JOIN tbConfigurationContributor cc3 on cc3.ConfigurationId = c.ConfigurationId
JOIN tbContributorRole cr3 on cr3.RoleId = cc3.RoleId
JOIN tbContributorName cn3 on cn3.ContributorId = cc3.ContributorId
AND IsDefaultName = 1
LEFT JOIN tbAmazonRoleMapping arm ON arm.RoleId = cr3.RoleId
WHERE cx3.ConfigurationId = c.ConfigurationId
FOR XML Path(contributor), ROOT(contributors), Type),
-- Music
(SELECT
(SELECT TOP 1 ISNULL(agm.AmazonGenreName, GenreDescription) AS Value
FROM tbConfiguration cx4
JOIN tbConfigurationGenre cg4 on cg4.ConfigurationId = cx4.ConfigurationId
JOIN tbGenre g4 on g4.GenreId = cg4.GenreId
LEFT JOIN tbAmazonGenreMapping agm ON g4.GenreId = agm.GenreId
WHERE cx4.ConfigurationId = c.ConfigurationId
FOR XML Path(genre), Type),
(CASE ParentalAdvisoryIndicatorLyrics
WHEN N THEN
False
WHEN Y THEN
True
END) AS isExplicitLyrics,
(SELECT VersionTitle AS value,
(CASE CompilationIndicator WHEN Y THEN Complitation ELSE NULL END) AS value
FROM tbConfiguration cx5
WHERE cx5.ConfigurationId = c.ConfigurationId
AND (CompilationIndicator = Y
OR versiontitle IS NOT NULL)
FOR XML Path(contentType), type)
FROM tbConfiguration cx5
WHERE cx5.ConfigurationId = c.ConfigurationId
FOR XML Path(music), Type),
-- Owner Info
(SELECT EMI AS corporateGroup,
RTRIM(phi1.Label) AS Company
FROM tbSAPPhysicalImport phi1 (NOLOCK)
WHERE phi1.ICPN = c.ICPN
FOR XML Path(ownerInfo), Type),
(CASE RTRIM(LTRIM(DeleteDate)) WHEN 00000000 THEN
CASE WHEN CONVERT(DATETIME, ReleaseDate, 101) > GETDATE() THEN Not yet published
ELSE In print
END
ELSE Out of print END) AS lifeCycle,
-- (CASE phi.LifeCycle WHEN 1 THEN Available ELSE Not Available END) AS lifeCycle,
-- Run Time
(CASE
WHEN LEN(CONVERT(VARCHAR(5), c.TotalMusicTime / 3600)) = 1 THEN
0+ CONVERT(VARCHAR(5), c.TotalMusicTime / 3600) + :
ELSE CONVERT(VARCHAR(5), c.TotalMusicTime / 3600) + : END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60)) = 1 THEN
0 + CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60) + :
ELSE CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60) + : END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), (c.TotalMusicTime % 60))) = 1 THEN
0 + CONVERT(VARCHAR(5), (c.TotalMusicTime % 60))
ELSE CONVERT(VARCHAR(5), (c.TotalMusicTime % 60)) END) AS runtime,
(LEFT(phi.ReleaseDate,4) + - + SUBSTRING(phi.ReleaseDate,5,2) + - + SUBSTRING(phi.ReleaseDate,7,2)) AS releaseDate,
--Original Release Date
(SELECT CONVERT(VARCHAR(10), MIN(LocalReleaseDate),120)
FROM tbProductExploitation pex
WHERE pex.ConfigurationId = c.ConfigurationId) AS originalReleaseDate,
-- AS sellable,
(SELECT phi1.DealerPrice AS Price,
phi1.Currency AS currencyUnit
FROM tbSAPPhysicalImport (NOLOCK) phi1
JOIN tbConfiguration cx7 on cx7.ICPN = phi1.ICPN
WHERE cx7.ConfigurationId = c.ConfigurationId
FOR XML Path(priceWithCurrency), ROOT(listPriceWithoutTax), type),
(SELECT phi2.DealerPrice AS price,
phi2.Currency AS currencyUnit
FROM tbSAPPhysicalImport (NOLOCK) phi2
JOIN tbConfiguration cx8 on cx8.ICPN = phi2.ICPN
WHERE cx8.ConfigurationId = c.ConfigurationId
FOR XML Path(priceWithCurrency), ROOT(listPriceWithTax), type),
(CASE c.BoxedSetIndicator
WHEN 1 THEN (SELECT COUNT(*) FROM tbBoxedSetComponent WHERE boxedSetConfigId = c.ConfigurationId)
ELSE (SELECT COUNT(*) FROM tbComponent WHERE ConfigurationId = c.ConfigurationId)
END) AS numberOfItems,
-- Tracks
(SELECT r6.ISRC AS isrcCode,
r6.repertoireTitle AS title,
(SELECT * FROM
(SELECT ISNULL(arm1.AmazonRoleName, cr7.RoleName) AS role,
cn7.Name AS name
FROM tbRecording r7
JOIN tbRecordingContributor rc7 on rc7.RecordingId = r7.RecordingId
AND RoleId IN (1, 2)
JOIN tbContributorRole cr7 on cr7.RoleId = rc7.RoleId
JOIN tbContributorName cn7 on cn7.ContributorId = rc7.ContributorId
AND IsDefaultName = 1
LEFT JOIN tbAmazonRoleMapping arm1 ON arm1.RoleId = cr7.RoleId
WHERE r7.recordingID = r6.recordingID
UNION
SELECT ISNULL(arm2.AmazonRoleName, cr8.RoleName) AS role,
cn8.Name AS Name
FROM tbRecording r8
JOIN tbRecordingComposition rc8 on rc8.RecordingId = r8.RecordingId
JOIN tbCompositionContributor cc8 on cc8.CompositionId = rc8.CompositionId
JOIN tbContributorRole cr8 on cr8.RoleId = cc8.RoleId
JOIN tbContributorName cn8 on cn8.ContributorId = cc8.ContributorId
LEFT JOIN tbAmazonRoleMapping arm2 ON arm2.RoleId = cr8.RoleId
WHERE r8.RecordingId = r6.RecordingId) a
FOR XML Path(contributor), ROOT(contributors), Type),
t6.ComponentNumber AS discNumber,
t6.SideNumber AS sideNumber,
t6.LogicalTrackNumber AS trackNumber,
(CASE
WHEN LEN(CONVERT(VARCHAR(5), t6.TrackTiming / 3600)) = 1 THEN
0+ CONVERT(VARCHAR(5), t6.TrackTiming / 3600) + :
ELSE CONVERT(VARCHAR(5), t6.TrackTiming / 3600) + : END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60)) = 1 THEN
0 + CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60) + :
ELSE CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60) + : END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), (t6.TrackTiming % 60))) = 1 THEN
0 + CONVERT(VARCHAR(5), (t6.TrackTiming % 60))
ELSE CONVERT(VARCHAR(5), (t6.TrackTiming % 60)) END) AS trackDuration
FROM tbConfiguration cx6
JOIN tbtrack t6 on t6.ConfigurationId = cx6.ConfigurationId
JOIN tbrecording r6 on r6.recordingID = t6.recordingiD
WHERE cx6.ConfigurationId = c.ConfigurationId
FOR XML PATH(track), ROOT(tracks), Type)
FROM tbConfiguration c
JOIN tbSAPPhysicalImport (NOLOCK) Phi ON phi.ICPN = c.ICPN
JOIN tbconfigurationFormat cf ON cf.FormatId = c.FormatId
LEFT JOIN tbAmazonFormatMapping afm ON afm.FormatId = cf.FormatId
FOR XML PATH(mediaItem), Type)
FOR XML PATH(mediaFeed), Type
)
[/code]
<br/>
View the full article
<span><?<span>xml version="1.0" encoding="UTF-8" <span>?>
<a rel="nofollow - <span><!-- <span>
<pre> This Document was last updated on 20-July-2011 14:41 IST [/code]
<span> <span>-->
<div style="text-indent:-2em http://acff-documentation.s3.amazonaws.com/ACFF_XML_Sample.xml# -
<span><<span>mediaFeed<span> xmlns:xsi<span>=" http://www.w3.org/2001/XMLSchema-instance <span>"<span> xsi:noNamespaceSchemaLocation<span>=" AMF-XML-Schema.xsd <span>"<span>>
from the following piece of code. I just need the header section correct, at the moment the following XML tags are attached to each ROOT defined in the XML i.e.
<div style="text-indent:-2em <span><span> xmlns:xsi<span>=" http://www.w3.org/2001/XMLSchema-instance <span>"<span>>
<div style="text-indent:-2em <span><span>I If any one can help I would be most appreciated.
<div style="text-indent:-2em <span><span> Thanks
<div style="text-indent:-2em <span><br/>
<div style="text-indent:-2em
<div style="text-indent:-2em <span>
<pre>SELECT (
SELECT AMF-XML-Schema.xsd AS noNamespaceSchemaLocation,
(SELECT
(SELECT ICPN AS EAN
FROM tbConfiguration cx1
WHERE cx1.ConfigurationId = c.ConfigurationId
FOR XML Path(itemIdentifier), TYPE),
(SELECT ConfigurationTitle AS Title
FROM tbConfiguration cx2
WHERE cx2.ConfigurationId = c.ConfigurationId
FOR XML Path(OriginalLanguage), ROOT(title), Type),
ISNULL(afm.AmazonFormatName, cf.FormatName) AS productForm,
(SELECT ISNULL(arm.AmazonRoleName, cr3.RoleName) AS Role,
cn3.Name AS Name
FROM tbConfiguration cx3
JOIN tbConfigurationContributor cc3 on cc3.ConfigurationId = c.ConfigurationId
JOIN tbContributorRole cr3 on cr3.RoleId = cc3.RoleId
JOIN tbContributorName cn3 on cn3.ContributorId = cc3.ContributorId
AND IsDefaultName = 1
LEFT JOIN tbAmazonRoleMapping arm ON arm.RoleId = cr3.RoleId
WHERE cx3.ConfigurationId = c.ConfigurationId
FOR XML Path(contributor), ROOT(contributors), Type),
-- Music
(SELECT
(SELECT TOP 1 ISNULL(agm.AmazonGenreName, GenreDescription) AS Value
FROM tbConfiguration cx4
JOIN tbConfigurationGenre cg4 on cg4.ConfigurationId = cx4.ConfigurationId
JOIN tbGenre g4 on g4.GenreId = cg4.GenreId
LEFT JOIN tbAmazonGenreMapping agm ON g4.GenreId = agm.GenreId
WHERE cx4.ConfigurationId = c.ConfigurationId
FOR XML Path(genre), Type),
(CASE ParentalAdvisoryIndicatorLyrics
WHEN N THEN
False
WHEN Y THEN
True
END) AS isExplicitLyrics,
(SELECT VersionTitle AS value,
(CASE CompilationIndicator WHEN Y THEN Complitation ELSE NULL END) AS value
FROM tbConfiguration cx5
WHERE cx5.ConfigurationId = c.ConfigurationId
AND (CompilationIndicator = Y
OR versiontitle IS NOT NULL)
FOR XML Path(contentType), type)
FROM tbConfiguration cx5
WHERE cx5.ConfigurationId = c.ConfigurationId
FOR XML Path(music), Type),
-- Owner Info
(SELECT EMI AS corporateGroup,
RTRIM(phi1.Label) AS Company
FROM tbSAPPhysicalImport phi1 (NOLOCK)
WHERE phi1.ICPN = c.ICPN
FOR XML Path(ownerInfo), Type),
(CASE RTRIM(LTRIM(DeleteDate)) WHEN 00000000 THEN
CASE WHEN CONVERT(DATETIME, ReleaseDate, 101) > GETDATE() THEN Not yet published
ELSE In print
END
ELSE Out of print END) AS lifeCycle,
-- (CASE phi.LifeCycle WHEN 1 THEN Available ELSE Not Available END) AS lifeCycle,
-- Run Time
(CASE
WHEN LEN(CONVERT(VARCHAR(5), c.TotalMusicTime / 3600)) = 1 THEN
0+ CONVERT(VARCHAR(5), c.TotalMusicTime / 3600) + :
ELSE CONVERT(VARCHAR(5), c.TotalMusicTime / 3600) + : END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60)) = 1 THEN
0 + CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60) + :
ELSE CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60) + : END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), (c.TotalMusicTime % 60))) = 1 THEN
0 + CONVERT(VARCHAR(5), (c.TotalMusicTime % 60))
ELSE CONVERT(VARCHAR(5), (c.TotalMusicTime % 60)) END) AS runtime,
(LEFT(phi.ReleaseDate,4) + - + SUBSTRING(phi.ReleaseDate,5,2) + - + SUBSTRING(phi.ReleaseDate,7,2)) AS releaseDate,
--Original Release Date
(SELECT CONVERT(VARCHAR(10), MIN(LocalReleaseDate),120)
FROM tbProductExploitation pex
WHERE pex.ConfigurationId = c.ConfigurationId) AS originalReleaseDate,
-- AS sellable,
(SELECT phi1.DealerPrice AS Price,
phi1.Currency AS currencyUnit
FROM tbSAPPhysicalImport (NOLOCK) phi1
JOIN tbConfiguration cx7 on cx7.ICPN = phi1.ICPN
WHERE cx7.ConfigurationId = c.ConfigurationId
FOR XML Path(priceWithCurrency), ROOT(listPriceWithoutTax), type),
(SELECT phi2.DealerPrice AS price,
phi2.Currency AS currencyUnit
FROM tbSAPPhysicalImport (NOLOCK) phi2
JOIN tbConfiguration cx8 on cx8.ICPN = phi2.ICPN
WHERE cx8.ConfigurationId = c.ConfigurationId
FOR XML Path(priceWithCurrency), ROOT(listPriceWithTax), type),
(CASE c.BoxedSetIndicator
WHEN 1 THEN (SELECT COUNT(*) FROM tbBoxedSetComponent WHERE boxedSetConfigId = c.ConfigurationId)
ELSE (SELECT COUNT(*) FROM tbComponent WHERE ConfigurationId = c.ConfigurationId)
END) AS numberOfItems,
-- Tracks
(SELECT r6.ISRC AS isrcCode,
r6.repertoireTitle AS title,
(SELECT * FROM
(SELECT ISNULL(arm1.AmazonRoleName, cr7.RoleName) AS role,
cn7.Name AS name
FROM tbRecording r7
JOIN tbRecordingContributor rc7 on rc7.RecordingId = r7.RecordingId
AND RoleId IN (1, 2)
JOIN tbContributorRole cr7 on cr7.RoleId = rc7.RoleId
JOIN tbContributorName cn7 on cn7.ContributorId = rc7.ContributorId
AND IsDefaultName = 1
LEFT JOIN tbAmazonRoleMapping arm1 ON arm1.RoleId = cr7.RoleId
WHERE r7.recordingID = r6.recordingID
UNION
SELECT ISNULL(arm2.AmazonRoleName, cr8.RoleName) AS role,
cn8.Name AS Name
FROM tbRecording r8
JOIN tbRecordingComposition rc8 on rc8.RecordingId = r8.RecordingId
JOIN tbCompositionContributor cc8 on cc8.CompositionId = rc8.CompositionId
JOIN tbContributorRole cr8 on cr8.RoleId = cc8.RoleId
JOIN tbContributorName cn8 on cn8.ContributorId = cc8.ContributorId
LEFT JOIN tbAmazonRoleMapping arm2 ON arm2.RoleId = cr8.RoleId
WHERE r8.RecordingId = r6.RecordingId) a
FOR XML Path(contributor), ROOT(contributors), Type),
t6.ComponentNumber AS discNumber,
t6.SideNumber AS sideNumber,
t6.LogicalTrackNumber AS trackNumber,
(CASE
WHEN LEN(CONVERT(VARCHAR(5), t6.TrackTiming / 3600)) = 1 THEN
0+ CONVERT(VARCHAR(5), t6.TrackTiming / 3600) + :
ELSE CONVERT(VARCHAR(5), t6.TrackTiming / 3600) + : END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60)) = 1 THEN
0 + CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60) + :
ELSE CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60) + : END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), (t6.TrackTiming % 60))) = 1 THEN
0 + CONVERT(VARCHAR(5), (t6.TrackTiming % 60))
ELSE CONVERT(VARCHAR(5), (t6.TrackTiming % 60)) END) AS trackDuration
FROM tbConfiguration cx6
JOIN tbtrack t6 on t6.ConfigurationId = cx6.ConfigurationId
JOIN tbrecording r6 on r6.recordingID = t6.recordingiD
WHERE cx6.ConfigurationId = c.ConfigurationId
FOR XML PATH(track), ROOT(tracks), Type)
FROM tbConfiguration c
JOIN tbSAPPhysicalImport (NOLOCK) Phi ON phi.ICPN = c.ICPN
JOIN tbconfigurationFormat cf ON cf.FormatId = c.FormatId
LEFT JOIN tbAmazonFormatMapping afm ON afm.FormatId = cf.FormatId
FOR XML PATH(mediaItem), Type)
FOR XML PATH(mediaFeed), Type
)
[/code]
<br/>
View the full article