|
|
 next
|
  Subject: Best Way To Generate Complex XML Doc From Spreadsheet - Help!
Author: Wilhelm Beck
Date: 20 Jul 2012 07:05 PM
Originally Posted: 20 Jul 2012 06:44 PM
|
Best Way To Generate Complex XML Doc From Spreadsheet - Help!
Hello! So, first off I'm not really a "real" programmer. I handle a lot of tech stuff at the company I'm with though, and I'm pretty good at dabbling. I usually figure out how to do what I need to do when I need to do it, and I rarely can't figure things out! I know plenty of HTML, how to work with CSS style sheets, all kinds of fancy Excel formulas, XML etc.
Sooo, the thing is, I now kind of need to figure out something new, and it's a little more in depth than other things I've tried to do in the past and I'm not sure the best way to go about it.
So here's the score. I work for a record label, and we provide metadata for album releases to numerous sites in XML. We've been doing this for years, and the way most XML files display in Grid mode in Stylus has made it so easy to just copy and paste from Excel I've never seen the need to figure out a "proper" or more automated system. In the long haul I probably should have because it would have saved time in the long run, but we really custom tailor our XML to each sites system to optimize it, which requires a human touch anyway.
Enter a new site we're delivering directly to. Their XML is very complicated and lethargic. There's a lot of duplicated data in different tags that shouldn't really exist, but are required. It's to the point of ridiculousness really. It'd be waaay too much work to manually enter things, even with find/replace.
So I started looking for a solution to automate it. I have kind of found a way. Basically I used Stylus to convert a CSV for an album to XML that looks like this
<root>
<fieldnames>
<UPC>UPC</UPC>
<catalog>Catalog Number</catalog>
<tracknumber>Track Number</tracknumber>
<release>Release Date</release>
<albumtitle>Title</albumtitle>
<artist>Artist</artist>
<tracktitle>Track Title</tracktitle>
<isrc>ISRC USM2U</isrc>
<description>Description</description>
<time1>Track Times</time1>
<field>Source Master</field>
<field>Notes</field>
<field>Song Writers</field>
</fieldnames>
<Row>
<UPC>XXXXXXXXX</UPC>
<catalog> CD119</catalog>
<tracknumber>01</tracknumber>
<release>4/10/2003</release>
<albumtitle>XXXXXXXXXX</albumtitle>
<artist>YYYYYYYYYYYYY</artist>
<tracktitle>ZZZZZZZZZZZZ</tracktitle>
<isrc>XXXXXXXXXX</isrc>
<time1>2:49</time1>
<time2>0:02:49</time2>
<time3>169</time3>
<field>169000</field>
</Row>
Then that repeats for all tracks
Then I found Xquery code
{
let $doc := .
for $v in $doc//root/Row,
$a in $doc//root/Row/tracknumber/text()
where matches($a, '01')
and $v/tracknumber/text() = $a
return $v/UPC/text()
}
Which can pull data from whatever field based off of the track number. This would allow it to pull the track name, track time, etc on a per track basis. So in essence I could setup
<File>
<FileName>{
let $doc := .
for $v in $doc//root/Row,
$a in $doc//root/Row/tracknumber/text()
where matches($a, '2')
and $v/tracknumber/text() = $a
return $v/UPC/text()
}_01_01_FLAC.flac</FileName>
<FilePath>resources</FilePath>
<HashSum>
<HashSum>CHECKSUM SONG 01</HashSum>
<HashSumAlgorithmType>MD5</HashSumAlgorithmType>
</HashSum>
</File>
Etc for each field that would pull the appropriate data in a query that features the actual XML in there as well, so the end result of a full query would be a valid XML document.
The downside is that I have to have a manual set of XML code like the above in each spot where I need to fill something. If I created a query with say 30 tracks it would be enough to always auto fill all our albums... Except it would also leave a bunch of XML with empty fields if there were less than 30 tracks.
So I'm wondering 2 things. Should I even use Xquery. It really seems like a ghetto hack way of going about this, especially because of having to manually create a CSV and import that album by album. If not, what should I use? We have Access, and MS SQL floating around. It seems like if I could get similar functionality as far as kicking out text out of either of those that alone would advance the cause considerably since they natively handle Excel files. Especially if I could make it automatically recognize when the UPC field changed in a single big long Excel spreadsheet but kick out separate text (XML) documents.
As far as if Xquery is a valid way of going about this, can I embed the actual XML code portion in an "if then" sort of situation where it would NOT create data in the final query result if the track number didn't exist in the source XML created from the CSV? Or is there no way to include straight XML inside of code like that?
If that is possible, I think even the ghetto Xquery way would be workable, otherwise there will be a lot of manual deletion in this big complex XML document that will have to be deleted out. That would still be easier than doing it completely manually, but certainly not optimal.
As far as Access or SQL... I've never done anything much in there, but have worked with people who have done all kinds of awesome stuff with them. I know that SQL especially can create all of kinds of text being populated from spreadsheets.
It seems to me like there must be some kind of way to make one of them generate the XML tag I need, then pull from a particular field in a spreadsheet that's in the system, and only do it until the data ends or it reaches a new UPC in the spreadsheet. Perhaps create a separate table with all the XML start tags and end tags, so it's actually pulling everything from certain fields in spreadsheets to create the final product?
I know this is a bit long winded, so sorry for that. I should have been a Russian novelist ;) And I'm sure this is pretty novice stuff, but I've found programmers are usually quite willing to help in the past. Since this must be pretty simple, I'm hoping I can figure this out and permanently expand my bag of tricks! All I really need is a way to auto pull fields based on perhaps a UPC/track number combo, and have it end when data stops. I could even set this up for all the other sites we deliver to and streamline things even more!
Any thoughts or suggestions are welcome, and thank all of you in advance for your help!!!
|
 top
|
Subject: Best Way To Generate Complex XML Doc From Spreadsheet - Help!
Author: Wilhelm Beck
Date: 20 Jul 2012 09:35 PM
Originally Posted: 20 Jul 2012 09:33 PM
|
Hi Ivan!
Thanks for responding! We keep our data in a simple Excel file, and convert it to other formats as needed. Someday a big fancy database system would be good, but for now it works!
It's columns are the fields (UPC, Artist, Album Name Etc), with the rows being the same data repeated where it's the same (UPC, Artist etc) and the track numbers, times, and so on different on every succeeding row. Example below, with some fields omitted.
UPC Catalog Number Track Number Release Date Title
UPC 01 CD100 01 4/10/2003 Song Title 1
UPC 01 CD100 02 4/10/2003 Song Title 2
UPC 02 CD101 01 5/10/2003 Song Title 1
UPC 02 CD101 02 5/10/2003 Song Title 2
An example of how I converted the spreadsheet into XML is below. If there's a better way, or it is unnecessary that would be amazing! Being able to pull straight from Excel, or even a CSV, into Xquery would be great.
<?xml version="1.0" encoding="utf-8"?>
<root>
<fieldnames>
<UPC>UPC</UPC>
<catalog>Catalog Number</catalog>
<tracknumber>Track Number</tracknumber>
<release>Release Date</release>
<albumtitle>Title</albumtitle>
<artist>Artist</artist>
<tracktitle>Track Title</tracktitle>
<isrc>ISRC USM2U</isrc>
<description>Description</description>
<time1>Track Times</time1>
<field>Source Master</field>
<field>Notes</field>
<field>Song Writers</field>
</fieldnames>
<Row>
<UPC>A UPC NUMBER</UPC>
<catalog>CD119</catalog>
<tracknumber>01</tracknumber>
<release>4/10/2003</release>
<albumtitle>An Album Title</albumtitle>
<artist>An Artist Name</artist>
<tracktitle>Track 01</tracktitle>
<isrc>THE ISRC</isrc>
<time1>2:49</time1>
<time2>0:02:49</time2>
<time3>169</time3>
<field>169000</field>
</Row>
<Row>
<UPC>A UPC NUMBER</UPC>
<catalog>CD119</catalog>
<tracknumber>02</tracknumber>
<release>4/10/2003</release>
<albumtitle>An Album Title</albumtitle>
<artist>An Artist Name</artist>
<tracktitle>Track 01</tracktitle>
<isrc>THE ISRC</isrc>
<time1>3:57</time1>
<time2>0:03:57</time2>
<time3>237</time3>
<field>237000</field>
</Row>
</root>
Then I will post a full sample of the schema it needs to go into
<?xml version="1.0" encoding="UTF-8"?>
<ern:NewReleaseMessage MessageSchemaVersionId="2010/ern-main/32" LanguageAndScriptCode="en" xs:schemaLocation="http://ddex.net/xml/2010/ern-main/32 http://ddex.net/xml/2010/ern-main/32/ern-main.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" xmlns:ern="http://ddex.net/xml/2010/ern-main/32">
<MessageHeader>
<MessageThreadId>20120707113545146</MessageThreadId>
<MessageId>THIS IS THE UPC1</MessageId>
<MessageSender>
<PartyId>PADPIDA2012070305K</PartyId>
<PartyName>
<FullName>DON'T NEED THIS!</FullName>
</PartyName>
</MessageSender>
<MessageRecipient>
<PartyId>PADPIDA2010120902Y</PartyId>
<PartyName>
<FullName>DON'T NEED THIS!</FullName>
</PartyName>
</MessageRecipient>
<MessageCreatedDateTime>2012-07-07T11:35:45</MessageCreatedDateTime>
<MessageControlType>LiveMessage</MessageControlType>
</MessageHeader>
<UpdateIndicator xmlns="">OriginalMessage</UpdateIndicator>
<ResourceList>
<SoundRecording>
<SoundRecordingType>MusicalWorkSoundRecording</SoundRecordingType>
<SoundRecordingId>
<ISRC>ISRC01</ISRC>
</SoundRecordingId>
<ResourceReference>A1</ResourceReference>
<ReferenceTitle>
<TitleText>SONG TITLE 01</TitleText>
</ReferenceTitle>
<Duration>PT0H3M16S</Duration>
<CreationDate>2012-07-07</CreationDate>
<SoundRecordingDetailsByTerritory>
<TerritoryCode>Worldwide</TerritoryCode>
<Title TitleType="DisplayTitle">
<TitleText>SONG TITLE 01</TitleText>
</Title>
<DisplayArtist>
<PartyName>
<FullName>MAIN ARTIST 01</FullName>
</PartyName>
</DisplayArtist>
<LabelName>Label Name Records</LabelName>
<OriginalResourceReleaseDate>2012-07-07</OriginalResourceReleaseDate>
<PLine>
<Year>2012</Year>
<PLineText>2012 Label Name Records</PLineText>
</PLine>
<Genre>
<GenreText>Jazz</GenreText>
<SubGenre>Avant Garde Jazz</SubGenre>
</Genre>
<ParentalWarningType>NotExplicit</ParentalWarningType>
<TechnicalSoundRecordingDetails>
<TechnicalResourceDetailsReference>T1</TechnicalResourceDetailsReference>
<AudioCodecType UserDefinedValue="FLAC">UserDefined</AudioCodecType>
<NumberOfChannels>2</NumberOfChannels>
<SamplingRate UnitOfMeasure="kHz">44100</SamplingRate>
<BitsPerSample>16</BitsPerSample>
<IsPreview>false</IsPreview>
<File>
<FileName>THIS IS THE UPC_01_01_FLAC.flac</FileName>
<FilePath>resources</FilePath>
<HashSum>
<HashSum>CHECKSUM SONG 01</HashSum>
<HashSumAlgorithmType>MD5</HashSumAlgorithmType>
</HashSum>
</File>
</TechnicalSoundRecordingDetails>
</SoundRecordingDetailsByTerritory>
</SoundRecording>
<SoundRecording>
<SoundRecordingType>MusicalWorkSoundRecording</SoundRecordingType>
<SoundRecordingId>
<ISRC>ISRC02</ISRC>
</SoundRecordingId>
<ResourceReference>A2</ResourceReference>
<ReferenceTitle>
<TitleText>SONG TITLE 02</TitleText>
</ReferenceTitle>
<Duration>PT0H2M48S</Duration>
<CreationDate>2012-07-07</CreationDate>
<SoundRecordingDetailsByTerritory>
<TerritoryCode>Worldwide</TerritoryCode>
<Title TitleType="DisplayTitle">
<TitleText>SONG TITLE 02</TitleText>
</Title>
<DisplayArtist>
<PartyName>
<FullName>MAIN ARTIST 01</FullName>
</PartyName>
</DisplayArtist>
<LabelName>Label Name Records</LabelName>
<OriginalResourceReleaseDate>2012-07-07</OriginalResourceReleaseDate>
<PLine>
<Year>2012</Year>
<PLineText>2012 Label Name Records</PLineText>
</PLine>
<Genre>
<GenreText>Jazz</GenreText>
<SubGenre>Avant Garde Jazz</SubGenre>
</Genre>
<ParentalWarningType>NotExplicit</ParentalWarningType>
<TechnicalSoundRecordingDetails>
<TechnicalResourceDetailsReference>T2</TechnicalResourceDetailsReference>
<AudioCodecType UserDefinedValue="FLAC">UserDefined</AudioCodecType>
<SamplingRate UnitOfMeasure="kHz">44100</SamplingRate>
<BitsPerSample>16</BitsPerSample>
<IsPreview>false</IsPreview>
<File>
<FileName>THIS IS THE UPC_01_02_FLAC.flac</FileName>
<FilePath>resources</FilePath>
<HashSum>
<HashSum>CHECKSUM SONG 02</HashSum>
<HashSumAlgorithmType>MD5</HashSumAlgorithmType>
</HashSum>
</File>
</TechnicalSoundRecordingDetails>
</SoundRecordingDetailsByTerritory>
</SoundRecording>
<Image>
<ImageType>FrontCoverImage</ImageType>
<ImageId>
<ProprietaryId Namespace="DON'T NEED THIS!">THIS IS THE UPC.jpg</ProprietaryId>
</ImageId>
<ResourceReference>A101</ResourceReference>
<ImageDetailsByTerritory>
<TerritoryCode>Worldwide</TerritoryCode>
<TechnicalImageDetails>
<TechnicalResourceDetailsReference>T101</TechnicalResourceDetailsReference>
<ImageCodecType>JPEG</ImageCodecType>
<ImageHeight>1499</ImageHeight>
<ImageWidth>1484</ImageWidth>
<File>
<FileName>THIS IS THE UPC.jpg</FileName>
<FilePath>resources</FilePath>
<HashSum>
<HashSum>CHECKSUM ART 01</HashSum>
<HashSumAlgorithmType>MD5</HashSumAlgorithmType>
</HashSum>
</File>
</TechnicalImageDetails>
</ImageDetailsByTerritory>
</Image>
</ResourceList>
<ReleaseList>
<Release>
<ReleaseId>
<ICPN IsEan="false">THIS IS THE UPC</ICPN>
</ReleaseId>
<ReleaseReference>R0</ReleaseReference>
<ReferenceTitle>
<TitleText>The Jazz Age Volume 6</TitleText>
</ReferenceTitle>
<ReleaseResourceReferenceList>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A1</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A2</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A3</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A4</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A5</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A6</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A7</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A8</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A9</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A10</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A11</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A12</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A13</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A14</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A15</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A16</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A17</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A18</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A19</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A20</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A21</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A22</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A23</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A24</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A25</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A26</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A27</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A28</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A29</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A30</ReleaseResourceReference>
<ReleaseResourceReference ReleaseResourceType="SecondaryResource">A101</ReleaseResourceReference>
</ReleaseResourceReferenceList>
<ReleaseType>Album</ReleaseType>
<ReleaseDetailsByTerritory>
<TerritoryCode>Worldwide</TerritoryCode>
<DisplayArtistName>MAIN ARTIST 01</DisplayArtistName>
<LabelName>Label Name Records</LabelName>
<Title TitleType="DisplayTitle">
<TitleText>The Jazz Age Volume 6</TitleText>
</Title>
<DisplayArtist>
<PartyName>
<FullName>MAIN ARTIST 01</FullName>
</PartyName>
<ArtistRole>MainArtist</ArtistRole>
</DisplayArtist>
<ParentalWarningType>NotExplicit</ParentalWarningType>
<ResourceGroup>
<ResourceGroup>
<SequenceNumber>1</SequenceNumber>
<ResourceGroupContentItem>
<SequenceNumber>1</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A1</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>2</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A2</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>3</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A3</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>4</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A4</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>5</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A5</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>6</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A6</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>7</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A7</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>8</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A8</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>9</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A9</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>10</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A10</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>11</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A11</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>12</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A12</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>13</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A13</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>14</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A14</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>15</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A15</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>16</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A16</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>17</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A17</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>18</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A18</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>19</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A19</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>20</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A20</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>21</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A21</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>22</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A22</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>23</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A23</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>24</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A24</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>25</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A25</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>26</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A26</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>27</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A27</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>28</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A28</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>29</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A29</ReleaseResourceReference>
</ResourceGroupContentItem>
<ResourceGroupContentItem>
<SequenceNumber>30</SequenceNumber>
<ResourceType>SoundRecording</ResourceType>
<ReleaseResourceReference>A30</ReleaseResourceReference>
</ResourceGroupContentItem>
</ResourceGroup>
</ResourceGroup>
<Genre>
<GenreText>Jazz</GenreText>
<SubGenre>Avant Garde Jazz</SubGenre>
</Genre>
<OriginalReleaseDate>2012-07-07</OriginalReleaseDate>
<Keywords>KEYWORDS!!!</Keywords>
</ReleaseDetailsByTerritory>
<PLine>
<Year>2012</Year>
<PLineText>2012 Label Name Records</PLineText>
</PLine>
<CLine>
<Year>2012</Year>
<CLineText>2012 Label Name Records</CLineText>
</CLine>
</Release>
<Release>
<ReleaseId>
<ISRC>ISRC01</ISRC>
</ReleaseId>
<ReleaseReference>R1</ReleaseReference>
<ReferenceTitle>
<TitleText>SONG TITLE 01</TitleText>
</ReferenceTitle>
<ReleaseResourceReferenceList>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A1</ReleaseResourceReference>
</ReleaseResourceReferenceList>
<ReleaseType>TrackRelease</ReleaseType>
<ReleaseDetailsByTerritory>
<TerritoryCode>Worldwide</TerritoryCode>
<DisplayArtistName>MAIN ARTIST 01</DisplayArtistName>
<LabelName>Label Name Records</LabelName>
<Title TitleType="DisplayTitle">
<TitleText>SONG TITLE 01</TitleText>
</Title>
<DisplayArtist>
<PartyName>
<FullName>MAIN ARTIST 01</FullName>
</PartyName>
<ArtistRole>MainArtist</ArtistRole>
</DisplayArtist>
<ParentalWarningType>NotExplicit</ParentalWarningType>
<Genre>
<GenreText>Jazz</GenreText>
<SubGenre>Avant Garde Jazz</SubGenre>
</Genre>
<OriginalReleaseDate>2012-07-07</OriginalReleaseDate>
</ReleaseDetailsByTerritory>
<PLine>
<Year>2012</Year>
<PLineText>2012 Label Name Records</PLineText>
</PLine>
<CLine>
<Year>2012</Year>
<CLineText>2012 Label Name Records</CLineText>
</CLine>
</Release>
<Release>
<ReleaseId>
<ISRC>ISRC02</ISRC>
</ReleaseId>
<ReleaseReference>R2</ReleaseReference>
<ReferenceTitle>
<TitleText>SONG TITLE 02</TitleText>
</ReferenceTitle>
<ReleaseResourceReferenceList>
<ReleaseResourceReference ReleaseResourceType="PrimaryResource">A2</ReleaseResourceReference>
</ReleaseResourceReferenceList>
<ReleaseType>TrackRelease</ReleaseType>
<ReleaseDetailsByTerritory>
<TerritoryCode>Worldwide</TerritoryCode>
<DisplayArtistName>MAIN ARTIST 01</DisplayArtistName>
<LabelName>Label Name Records</LabelName>
<Title TitleType="DisplayTitle">
<TitleText>SONG TITLE 02</TitleText>
</Title>
<DisplayArtist>
<PartyName>
<FullName>MAIN ARTIST 01</FullName>
</PartyName>
<ArtistRole>MainArtist</ArtistRole>
</DisplayArtist>
<ParentalWarningType>NotExplicit</ParentalWarningType>
<Genre>
<GenreText>Avant Garde Jazz</GenreText>
<SubGenre>Avant Garde Jazz</SubGenre>
</Genre>
<OriginalReleaseDate>2012-07-07</OriginalReleaseDate>
</ReleaseDetailsByTerritory>
<PLine>
<Year>2012</Year>
<PLineText>2012 Label Name Records</PLineText>
</PLine>
<CLine>
<Year>2012</Year>
<CLineText>2012 Label Name Records</CLineText>
</CLine>
</Release>
</ReleaseList>
<DealList>
<ReleaseDeal>
<DealReleaseReference>R1</DealReleaseReference>
<Deal>
<DealTerms>
<CommercialModelType>AsPerContract</CommercialModelType>
<Usage>
<UseType UserDefinedValue="DSP NameMusicBasic">UserDefined</UseType>
</Usage>
<TerritoryCode>US</TerritoryCode>
<PriceInformation>
<PriceRangeType Namespace="DSP Name">.7</PriceRangeType>
</PriceInformation>
<ValidityPeriod>
<StartDate>2012-07-07</StartDate>
</ValidityPeriod>
<PreOrderReleaseDate>2012-07-07</PreOrderReleaseDate>
</DealTerms>
</Deal>
<EffectiveDate>2012-07-07</EffectiveDate>
</ReleaseDeal>
<ReleaseDeal>
<DealReleaseReference>R1</DealReleaseReference>
<Deal>
<DealTerms>
<CommercialModelType>AsPerContract</CommercialModelType>
<Usage>
<UseType UserDefinedValue="DSP NameMusicBasic">UserDefined</UseType>
</Usage>
<TerritoryCode>US</TerritoryCode>
<PriceInformation>
<PriceRangeType Namespace="DSP Name">.7</PriceRangeType>
</PriceInformation>
<ValidityPeriod>
<StartDate>2012-07-07</StartDate>
</ValidityPeriod>
<PreOrderReleaseDate>2012-07-07</PreOrderReleaseDate>
</DealTerms>
</Deal>
<EffectiveDate>2012-07-07</EffectiveDate>
</ReleaseDeal>
<ReleaseDeal>
<DealReleaseReference>R2</DealReleaseReference>
<Deal>
<DealTerms>
<CommercialModelType>AsPerContract</CommercialModelType>
<Usage>
<UseType UserDefinedValue="DSP NameMusicBasic">UserDefined</UseType>
</Usage>
<TerritoryCode>US</TerritoryCode>
<ValidityPeriod>
<StartDate>2012-07-07</StartDate>
</ValidityPeriod>
<PreOrderReleaseDate>2012-07-07</PreOrderReleaseDate>
</DealTerms>
</Deal>
<EffectiveDate>2012-07-07</EffectiveDate>
</ReleaseDeal>
</DealList>
</ern:NewReleaseMessage>
I told you it was long, repetitive and lethargic! Many sites we've uploaded to would have had 1/10th the fluff to get the same end results, which is why with the DDEX (an org trying to standardize how music related metadata is delivered) compliant XML stuff it is no longer feasible to do manually.
That schema would fully encompass a 2 track album, it's audio files, and it's art and validate correctly. Obviously I have "generic" values in all the fields. But it's all pretty straight forward. Our UPC needs to go where the schema needs a UPC, our track title where it requests that etc.
That little query code I posted in the first post will pull the correct data from my XML version version of our album data, however I can't seem to make it have the schema's XML tags inside of the Xquery code, where I might be able to make it's existence conditional based on finding a valid track number (which seems to be the best way to differentiate I've been able to come up with). There's probably some easy way to do this, but until a week ago I'd never even looked at Xquery code! So I think I'm doing OKAY :)
I don't need the whole thing written for me, I think if I can get one example of the code that could do the above, I could adapt it to all the other fields just fine.
So, as I said, any suggestions on how I could improve this process is certainly very welcome!!!
>
>Let's try keep things as
>simple as possible.
>
>If everything required by the
>target schema is available in
>the input data, no human
>intervention should be
>required.
>
>In which format you keep the
>data for the albums: CSV,
>Excel, a database?
>
>Our XQuery processor can
>access all the above without
>additional steps.
>
>Can you share a sample of the
>list of albums and target XML
>Schema?
>
>If you don't want to post on
>the public forum send it to
>stylussupport@ivitechnologies.
>com
>
>Hope this helps
>Ivan Pedruzzi
>Stylus Studio Team
|
|
|
|
|
