April 29, 2009 at 7:52 am
I have a problem I was hoping someone could help me with
Im builiding a custom cms for a clients website. Part of the requirement is to have a specific flash file associated with specific data that a user selects and to display the flash file on the webpage. The deisgner has opted to supply the flash files as three associated parts
the flash file itself (this basically displays a series of images)
an xml file that contains the name of each image displayed in the flash animation (the flash file reads the xml file to get the image to be displayed)
the image files
heres an example of the xml file
-
-
in the cms, the user will upload the flash file and the xml file. I subsequently want to be able to read the xml file on the server and move through each photo path in turn and insert a row in a table for each one that will hold the name of the image file. The user can then view the contents and manually upload each image to ensure all the images can be displayed.
Id like to do this in a stored procedure and inside a transaction. Is it possible to pass the original xml file into a stored procedure, parse it, and for each photo path, read the image name and insert into a table ? (so in the above example, id have 6 rows, each containing an image file name)
Im using visual studio 2008 and c# to build the cms
April 29, 2009 at 8:17 am
First, raw XML disappears when you post it in the forums. You need to use the code tags around it, with the XML type, or to attach it as a text file. Either one works.
SQL 2005 has the ability to read XML. Check out XQuery, with attention to the node() and value() functions, in Books Online.
If you can get the XML to display in the forum, or attach it, we can help with the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2009 at 8:36 am
In my last job, we worked with XML and SQL 2000, so I haven't had the experience of working with the XML data type that was new to SQL 2005; however, you can also pass your XML into a stored procedure as a text string and use sp_xml_preparedocument to open and insert the data. The downside, is that any XML validation for a well-formed document needs to be done outside of SQL, as do any XSLT transformations.
Linda
April 29, 2009 at 9:47 am
-
-
5
false
false
bgfade
207
162
window
-
April 29, 2009 at 9:48 am
"
-
-
5
false
false
bgfade
207
162
window
-
"
April 29, 2009 at 9:50 am
GSquared (4/29/2009)
First, raw XML disappears when you post it in the forums. You need to use the code tags around it, with the XML type, or to attach it as a text file. Either one works.SQL 2005 has the ability to read XML. Check out XQuery, with attention to the node() and value() functions, in Books Online.
If you can get the XML to display in the forum, or attach it, we can help with the query.
xml as a text file attached
April 29, 2009 at 9:52 am
anyone know how to post xml ???
ive tried saving to a text file and attaching it, but you cant subsequently view it !
April 29, 2009 at 9:56 am
jpgrotator>
parameters>
rotatetime>5
randomplay>false
shownavigation>false
transition>bgfade
width>207
height>162
wmode>window
/parameters>
photos>
photo path="image1.jpg" />
photo path="image2.jpg" />
photo path="image3.jpg" />
photo path="image4.jpg" />
photo path="image5.jpg" />
photo path="image6.jpg" />
photos>
jpgrotator>
ive had to remove all the "<" opening tags thats the only way to see the xml
April 29, 2009 at 9:57 am
Try prefixing it in the IFCode tag code
April 29, 2009 at 9:57 am
use the code tags
-
-
5
false
false
bgfade
207
162
window
-
April 29, 2009 at 10:01 am
Try the attached file in Management Studio. See if it does what you need.
You can use a column name instead of the @XML variable, if you're inserting the data into a table. That requires using Cross Apply, but it's easy to set up if you have the table data available.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2009 at 10:24 am
Alternative to GSquared's solution you also can directly select the values (without the .query('.') part):
--based on GSquared's solution in previous post
select Photos.Photo.value('(@path)[1]','varchar(100)') as PhotoPath
from @XML.nodes('(jpgrotator/photos/photo)') Photos(Photo)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply