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="sequenti al" 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].[GetSlideShowsBy Store3]

@StoreID int

AS

SELECT dbo.Stores.Stor eID, dbo.StoreSlideS how.ImagePath

FROM dbo.Stores INNER JOIN

dbo.StoreSlideS how ON dbo.Stores.Stor eID = dbo.StoreSlideS how.StoreID GO

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

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

document: (MyXmlFile3.xml )

<NewDataSet>

<Table>

<StoreID>1</StoreID>

<ImagePath>imag es2/Test1.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>imag es2/Test2.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>imag es2/Test3.jpg</ImagePath>

</Table>

....etc...

<Table>

<StoreID>2</StoreID>

<ImagePath>imag es2/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 ExecuteXmlReade r() 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="sequenti al" 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='ImagePa th'/></xs:attribute>
</image>
</xs:template>
HTH,
Christoph Schittko
MVP XML
http://weblogs.asp.net/cschittko

-----Original Message-----
From: Paolo Pignatelli [mailto:Pa***@Do tNetStore.com]
Posted At: Friday, December 17, 2004 7:15 AM
Posted To: microsoft.publi c.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="sequenti al" 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].[GetSlideShowsBy Store3]

@StoreID int

AS

SELECT dbo.Stores.Stor eID, dbo.StoreSlideS how.ImagePath

FROM dbo.Stores INNER JOIN

dbo.StoreSlideS how ON dbo.Stores.Stor eID = dbo.StoreSlideS how.StoreID GO
----------------------

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

document: (MyXmlFile3.xml )

<NewDataSet>

<Table>

<StoreID>1</StoreID>

<ImagePath>imag es2/Test1.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>imag es2/Test2.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>imag es2/Test3.jpg</ImagePath>

</Table>

...etc...

<Table>

<StoreID>2</StoreID>

<ImagePath>imag es2/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**********@a ustin.rr.com> wrote in message
news:#o******** ******@TK2MSFTN GP09.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 ExecuteXmlReade r() 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="sequenti al" 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='ImagePa th'/></xs:attribute>
</image>
</xs:template>
HTH,
Christoph Schittko
MVP XML
http://weblogs.asp.net/cschittko

-----Original Message-----
From: Paolo Pignatelli [mailto:Pa***@Do tNetStore.com]
Posted At: Friday, December 17, 2004 7:15 AM
Posted To: microsoft.publi c.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="sequenti al" 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].[GetSlideShowsBy Store3]

@StoreID int

AS

SELECT dbo.Stores.Stor eID, dbo.StoreSlideS how.ImagePath

FROM dbo.Stores INNER JOIN

dbo.StoreSlideS how ON dbo.Stores.Stor eID = dbo.StoreSlideS how.StoreID

GO

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

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

document: (MyXmlFile3.xml )

<NewDataSet>

<Table>

<StoreID>1</StoreID>

<ImagePath>imag es2/Test1.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>imag es2/Test2.jpg</ImagePath>

</Table>

<Table>

<StoreID>1</StoreID>

<ImagePath>imag es2/Test3.jpg</ImagePath>

</Table>

...etc...

<Table>

<StoreID>2</StoreID>

<ImagePath>imag es2/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,**