Changing format of XML Doc (using WriteXML?)

[Paolo Pignatelli]

I am trying to get an output/file like this (below) in an XML file
(MyXmlFile.xml) (which I will use for a slide show)

--

<gallery timer="3" order="sequential" fadetime="2" looping="yes" xpos="0"

ypos="0">

<image path="images2/Test1.jpg" />

<image path="images2/Test2.jpg" />

<image path="images2/Test3.jpg" />

<image path="images2/Test4.jpg" />

<image path="images2/Test5.jpg" />

<image path="images2/Test6.jpg" />

</gallery>

--

When I use this XML file (written by hand), I can get the Flash slide show
to work, provided that the jpgs are in the correct folder....

So, now I want to generate the slide choices automatically from SQL, so I
added a table called StoreSlideShow, and added a key column, a StoreID
column, and a column called ImagePath.

I filled in the data, and wrote the SPROC

CREATE PROCEDURE [dbo].[GetSlideShowsByStore3]

@StoreID int

AS

SELECT dbo.Stores.StoreID, dbo.StoreSlideShow.ImagePath

FROM dbo.Stores INNER JOIN

dbo.StoreSlideShow ON dbo.Stores.StoreID = dbo.StoreSlideShow.StoreID GO

----------------------

When I use the WriteXML command, etc..., it creates the following xml

document: (MyXmlFile3.xml)

<NewDataSet>

<Table>

<StoreID>1</StoreID>

<ImagePath>images2/Test1.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>images2/Test2.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>images2/Test3.jpg</ImagePath>

</Table>

....etc...

<Table>

<StoreID>2</StoreID>

<ImagePath>images2/Test1.jpg</ImagePath>

</Table>

</NewDataSet>

The data is correct, BUT - my dilemma- how do I get this output to match the
output/format above?

**TIA,**

[Christoph Schittko [MVP]]

Paolo,

Is there anything that you are selecting from the database that is part
of the <gallery> element? If so, you could very easily write a SQL
statement with the FOR XML AUTO clause and let SQL Server return XML in
the format you need to.

Unfortunately, in order for FOR XML AUTO to work you need at least one
attribute from the Stores table to work. Would your flash code break if
there was an extra attribute? If not you could write a statement like:

SELECT gallery.StoreID as id, '3' as timer, 'sequential' as order, '2'
as fadetime, 'yes' as looping, '0' as xpos, '0' as ypos, image.ImagePath
as 'path' FROM Stores gallery INNER JOIN StoreSlideShow image ON ... FOR
XML AUTO

And then execute the results via the ExecuteXmlReader() method on the
SqlCommand.

Again, for this to work you have to select at least on item in the
result set from the Stores table, otherwise you can't get the <gallery>
element in the response. I hope that an extra element will not throw off
your flash code.

If you can't go with that, then you could process the results with an
XSLT. It would go something like that (off the top of my head without
being able to validate it right now):

<xs:template match='/'>
<gallery timer="3" order="sequential" fadetime="2" looping="yes"
xpos="0" ypos="0">
<xs:apply-templates />
</gallery>
</xs:template>

<xs:template match='table'>
<image>
<xs:attribute name='path'><xs:value-of
select='ImagePath'/></xs:attribute>
</image>
</xs:template>
HTH,
Christoph Schittko
MVP XML
http://weblogs.asp.net/cschittko

-----Original Message-----
From: Paolo Pignatelli [mailto:Pa***@DotNetStore.com]
Posted At: Friday, December 17, 2004 7:15 AM
Posted To: microsoft.public.dotnet.xml
Conversation: Changing format of XML Doc (using WriteXML?)
Subject: Changing format of XML Doc (using WriteXML?)

I am trying to get an output/file like this (below) in an XML file
(MyXmlFile.xml) (which I will use for a slide show)

--

<gallery timer="3" order="sequential" fadetime="2" looping="yes" xpos="0"
ypos="0">

<image path="images2/Test1.jpg" />

<image path="images2/Test2.jpg" />

<image path="images2/Test3.jpg" />

<image path="images2/Test4.jpg" />

<image path="images2/Test5.jpg" />

<image path="images2/Test6.jpg" />

</gallery>

--

When I use this XML file (written by hand), I can get the Flash slide show to work, provided that the jpgs are in the correct folder....

So, now I want to generate the slide choices automatically from SQL, so I added a table called StoreSlideShow, and added a key column, a StoreID
column, and a column called ImagePath.

I filled in the data, and wrote the SPROC

CREATE PROCEDURE [dbo].[GetSlideShowsByStore3]

@StoreID int

AS

SELECT dbo.Stores.StoreID, dbo.StoreSlideShow.ImagePath

FROM dbo.Stores INNER JOIN

dbo.StoreSlideShow ON dbo.Stores.StoreID = dbo.StoreSlideShow.StoreID GO
----------------------

When I use the WriteXML command, etc..., it creates the following xml

document: (MyXmlFile3.xml)

<NewDataSet>

<Table>

<StoreID>1</StoreID>

<ImagePath>images2/Test1.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>images2/Test2.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>images2/Test3.jpg</ImagePath>

</Table>

...etc...

<Table>

<StoreID>2</StoreID>

<ImagePath>images2/Test1.jpg</ImagePath>

</Table>

</NewDataSet>

The data is correct, BUT - my dilemma- how do I get this output to match the
output/format above?

**TIA,**

[Paolo Pignatelli]

THANK YOU.

Let me go and work on this, and I may (probably will,) ask more questions.

Paolo
"Christoph Schittko [MVP]" <IN**********@austin.rr.com> wrote in message
news:#o**************@TK2MSFTNGP09.phx.gbl...

Paolo,

Is there anything that you are selecting from the database that is part
of the <gallery> element? If so, you could very easily write a SQL
statement with the FOR XML AUTO clause and let SQL Server return XML in
the format you need to.

Unfortunately, in order for FOR XML AUTO to work you need at least one
attribute from the Stores table to work. Would your flash code break if
there was an extra attribute? If not you could write a statement like:

SELECT gallery.StoreID as id, '3' as timer, 'sequential' as order, '2'
as fadetime, 'yes' as looping, '0' as xpos, '0' as ypos, image.ImagePath
as 'path' FROM Stores gallery INNER JOIN StoreSlideShow image ON ... FOR
XML AUTO

And then execute the results via the ExecuteXmlReader() method on the
SqlCommand.

Again, for this to work you have to select at least on item in the
result set from the Stores table, otherwise you can't get the <gallery>
element in the response. I hope that an extra element will not throw off
your flash code.

If you can't go with that, then you could process the results with an
XSLT. It would go something like that (off the top of my head without
being able to validate it right now):

<xs:template match='/'>
<gallery timer="3" order="sequential" fadetime="2" looping="yes"
xpos="0" ypos="0">
<xs:apply-templates />
</gallery>
</xs:template>

<xs:template match='table'>
<image>
<xs:attribute name='path'><xs:value-of
select='ImagePath'/></xs:attribute>
</image>
</xs:template>
HTH,
Christoph Schittko
MVP XML
http://weblogs.asp.net/cschittko

-----Original Message-----
From: Paolo Pignatelli [mailto:Pa***@DotNetStore.com]
Posted At: Friday, December 17, 2004 7:15 AM
Posted To: microsoft.public.dotnet.xml
Conversation: Changing format of XML Doc (using WriteXML?)
Subject: Changing format of XML Doc (using WriteXML?)

I am trying to get an output/file like this (below) in an XML file
(MyXmlFile.xml) (which I will use for a slide show)

--

<gallery timer="3" order="sequential" fadetime="2" looping="yes"

xpos="0"

ypos="0">

<image path="images2/Test1.jpg" />

<image path="images2/Test2.jpg" />

<image path="images2/Test3.jpg" />

<image path="images2/Test4.jpg" />

<image path="images2/Test5.jpg" />

<image path="images2/Test6.jpg" />

</gallery>

--

When I use this XML file (written by hand), I can get the Flash slide

show

to work, provided that the jpgs are in the correct folder....

So, now I want to generate the slide choices automatically from SQL,

so I

added a table called StoreSlideShow, and added a key column, a StoreID
column, and a column called ImagePath.

I filled in the data, and wrote the SPROC

CREATE PROCEDURE [dbo].[GetSlideShowsByStore3]

@StoreID int

AS

SELECT dbo.Stores.StoreID, dbo.StoreSlideShow.ImagePath

FROM dbo.Stores INNER JOIN

dbo.StoreSlideShow ON dbo.Stores.StoreID = dbo.StoreSlideShow.StoreID

GO

----------------------

When I use the WriteXML command, etc..., it creates the following xml

document: (MyXmlFile3.xml)

<NewDataSet>

<Table>

<StoreID>1</StoreID>

<ImagePath>images2/Test1.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>images2/Test2.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>images2/Test3.jpg</ImagePath>

</Table>

...etc...

<Table>

<StoreID>2</StoreID>

<ImagePath>images2/Test1.jpg</ImagePath>

</Table>

</NewDataSet>

The data is correct, BUT - my dilemma- how do I get this output to

match

the
output/format above?

**TIA,**