How to load XML from filename and path

Hi,

I have inherited a database that has a table called "Resources" that contains a field called "FilePath". FilePath is a VarChar(100) and contains the server path and filename of an XML file on the server hard disk.

I need to join the data in the XML with rows in the "Resources" table (and other tables).

Is it possible to get a Stored Procedure to load the XML file into a temporary table?

Is loading it into a table the best way to do it - or can I join directly to the XML file somehow?

What is the best approach? (I know nothing about XML in SQL Server yet)

Thanks in advance,

Chiz.

Try the link below to use OPENXML in SQL Server, I am not sure about Temp tables I would try it to see if it is possible, just remember to drop the temp table explictly. Hope this helps.
http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx|||Thanks Caddre,
At the moment the .NET code calls a Stored Procedure which JOINs the Resources table with some other tables and returns the result which goes into a DataSet.
In order to build a DataSet that can be displayed in a DataGrid the DataSet has to be looked at (one row at a time) and the FilePath 'field' used to load the XML file from disk into an XmlDocument. Then the right data in the right node has to be found and then finally when that is found it can be added to the DataSet.
Anyway, to cut a long story short, I reckon that if I can somehow get T-SQL to load an XML file from disk into a temporary table that it will be neater and more efficient.
Any other help appreciated,
Chiz.|||

The text below is from the BOL(books online) sp_xml_preparedocument System stored Procedure can be used, run a search for sp_xml_preparedocument in the BOL (books online). Hope this helps

(sp_xml_preparedocument
Reads the Extensible Markup Language (XML) text provided as input, then parses the text using the MSXML parser (Msxml2.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes (elements, attributes, text, comments, and so on) in the XML document.)

How to load images dinamically?

I want to show a different image on my header depending on some data on the
database, the path of the image could be on a table.
How can I do that?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 3.
MIEMBRO ACTIVO DE ALIANZADEV
http://spaces.msn.com/members/extremed/I would like to know this too. Rpt Designer allows you to use links for
images, but my question is similar. How can you change the value of the link?
"Luis Esteban Valencia" wrote:
> I want to show a different image on my header depending on some data on the
> database, the path of the image could be on a table.
> How can I do that?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>
>|||Hi Luis,
I figured out a way to do it using Links. Create a textbox and point it to
link stored in the dB. Create an image and under the image properties, set
it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
HTH
"Luis Esteban Valencia" wrote:
> I want to show a different image on my header depending on some data on the
> database, the path of the image could be on a table.
> How can I do that?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>
>|||Neo,
I tried what you suggested, but can't get it to work. I put
=ReportItems!txtPhotoLoc.Value in the Value property of the Image control.
The Source property is set to External. txtPhotoLoc is the name of a textbox
that has its value set to =Fields!PhotographLocation.Value.
Is this what you did? It doesn't work for me in either the preview or the
deployed version viewed through IE.
Is there something special about the "ReportItems" qualifier? I can't find
that object group anywhere in my project, but it doesn't complain about it
when I save and deploy it.
I checked and privileges are not a problem -- the content of
PhotographLocation it a fully qualified path to a URL you can paste in any
browser and get an image (e.g.,
http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
When I view source on the results in IE, the area that should show the
picture contains
<TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
Why does SRC = ""?
-prophead
"Neo" wrote:
> Hi Luis,
> I figured out a way to do it using Links. Create a textbox and point it to
> link stored in the dB. Create an image and under the image properties, set
> it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
> HTH
> "Luis Esteban Valencia" wrote:
> > I want to show a different image on my header depending on some data on the
> > database, the path of the image could be on a table.
> > How can I do that?
> >
> > --
> > LUIS ESTEBAN VALENCIA
> > MICROSOFT DCE 3.
> > MIEMBRO ACTIVO DE ALIANZADEV
> > http://spaces.msn.com/members/extremed/
> >
> >
> >|||Prophead,
In my report, I have an image placed in the page header and the report text
is placed in the body. I set the image value =ReportItems!txtImageLink.Value.
What is the value of Fields.PhotgraphLocation.Value? It doesn't look like
the field is being properly filled in. It should be something like
"http://mysite/images/img.gif".
BTW - SRC is the property of the Img html tag. Since the field is not being
populated correctly, it will be "" or null.
"prophead" wrote:
> Neo,
> I tried what you suggested, but can't get it to work. I put
> =ReportItems!txtPhotoLoc.Value in the Value property of the Image control.
> The Source property is set to External. txtPhotoLoc is the name of a textbox
> that has its value set to =Fields!PhotographLocation.Value.
> Is this what you did? It doesn't work for me in either the preview or the
> deployed version viewed through IE.
> Is there something special about the "ReportItems" qualifier? I can't find
> that object group anywhere in my project, but it doesn't complain about it
> when I save and deploy it.
> I checked and privileges are not a problem -- the content of
> PhotographLocation it a fully qualified path to a URL you can paste in any
> browser and get an image (e.g.,
> http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
> When I view source on the results in IE, the area that should show the
> picture contains
> <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
> Why does SRC = ""?
> -prophead
> "Neo" wrote:
> > Hi Luis,
> >
> > I figured out a way to do it using Links. Create a textbox and point it to
> > link stored in the dB. Create an image and under the image properties, set
> > it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
> >
> > HTH
> >
> > "Luis Esteban Valencia" wrote:
> >
> > > I want to show a different image on my header depending on some data on the
> > > database, the path of the image could be on a table.
> > > How can I do that?
> > >
> > > --
> > > LUIS ESTEBAN VALENCIA
> > > MICROSOFT DCE 3.
> > > MIEMBRO ACTIVO DE ALIANZADEV
> > > http://spaces.msn.com/members/extremed/
> > >
> > >
> > >|||Thanks, Neo. Installing SP1 fixed the problem. Evidently there was a
problem when you set the source to "External" and the Value to a field from
the db with a URL. I also deleted the original report from the server and
re-deployed it, so that might have fixed it as well.
-prophead
"Neo" wrote:
> Prophead,
> In my report, I have an image placed in the page header and the report text
> is placed in the body. I set the image value => ReportItems!txtImageLink.Value.
> What is the value of Fields.PhotgraphLocation.Value? It doesn't look like
> the field is being properly filled in. It should be something like
> "http://mysite/images/img.gif".
> BTW - SRC is the property of the Img html tag. Since the field is not being
> populated correctly, it will be "" or null.
>
> "prophead" wrote:
> > Neo,
> >
> > I tried what you suggested, but can't get it to work. I put
> > =ReportItems!txtPhotoLoc.Value in the Value property of the Image control.
> > The Source property is set to External. txtPhotoLoc is the name of a textbox
> > that has its value set to =Fields!PhotographLocation.Value.
> >
> > Is this what you did? It doesn't work for me in either the preview or the
> > deployed version viewed through IE.
> >
> > Is there something special about the "ReportItems" qualifier? I can't find
> > that object group anywhere in my project, but it doesn't complain about it
> > when I save and deploy it.
> >
> > I checked and privileges are not a problem -- the content of
> > PhotographLocation it a fully qualified path to a URL you can paste in any
> > browser and get an image (e.g.,
> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
> >
> > When I view source on the results in IE, the area that should show the
> > picture contains
> > <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
> > Why does SRC = ""?
> >
> > -prophead
> >
> > "Neo" wrote:
> >
> > > Hi Luis,
> > >
> > > I figured out a way to do it using Links. Create a textbox and point it to
> > > link stored in the dB. Create an image and under the image properties, set
> > > it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
> > >
> > > HTH
> > >
> > > "Luis Esteban Valencia" wrote:
> > >
> > > > I want to show a different image on my header depending on some data on the
> > > > database, the path of the image could be on a table.
> > > > How can I do that?
> > > >
> > > > --
> > > > LUIS ESTEBAN VALENCIA
> > > > MICROSOFT DCE 3.
> > > > MIEMBRO ACTIVO DE ALIANZADEV
> > > > http://spaces.msn.com/members/extremed/
> > > >
> > > >
> > > >|||The SP1 readme explains why it "works" on SP1. It is something that was
specifically added to SP1:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images
In particular, make sure you read the comment about configuring the
UnattendedExecutionAccount in the readme notes.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"prophead" <prophead@.discussions.microsoft.com> wrote in message
news:DC1075B2-4261-419D-9ABD-5ED96C5A0E2F@.microsoft.com...
> Thanks, Neo. Installing SP1 fixed the problem. Evidently there was a
> problem when you set the source to "External" and the Value to a field
> from
> the db with a URL. I also deleted the original report from the server and
> re-deployed it, so that might have fixed it as well.
> -prophead
> "Neo" wrote:
>> Prophead,
>> In my report, I have an image placed in the page header and the report
>> text
>> is placed in the body. I set the image value =>> ReportItems!txtImageLink.Value.
>> What is the value of Fields.PhotgraphLocation.Value? It doesn't look
>> like
>> the field is being properly filled in. It should be something like
>> "http://mysite/images/img.gif".
>> BTW - SRC is the property of the Img html tag. Since the field is not
>> being
>> populated correctly, it will be "" or null.
>>
>> "prophead" wrote:
>> > Neo,
>> >
>> > I tried what you suggested, but can't get it to work. I put
>> > =ReportItems!txtPhotoLoc.Value in the Value property of the Image
>> > control.
>> > The Source property is set to External. txtPhotoLoc is the name of a
>> > textbox
>> > that has its value set to =Fields!PhotographLocation.Value.
>> >
>> > Is this what you did? It doesn't work for me in either the preview or
>> > the
>> > deployed version viewed through IE.
>> >
>> > Is there something special about the "ReportItems" qualifier? I can't
>> > find
>> > that object group anywhere in my project, but it doesn't complain about
>> > it
>> > when I save and deploy it.
>> >
>> > I checked and privileges are not a problem -- the content of
>> > PhotographLocation it a fully qualified path to a URL you can paste in
>> > any
>> > browser and get an image (e.g.,
>> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
>> >
>> > When I view source on the results in IE, the area that should show the
>> > picture contains
>> > <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
>> > Why does SRC = ""?
>> >
>> > -prophead
>> >
>> > "Neo" wrote:
>> >
>> > > Hi Luis,
>> > >
>> > > I figured out a way to do it using Links. Create a textbox and point
>> > > it to
>> > > link stored in the dB. Create an image and under the image
>> > > properties, set
>> > > it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
>> > >
>> > > HTH
>> > >
>> > > "Luis Esteban Valencia" wrote:
>> > >
>> > > > I want to show a different image on my header depending on some
>> > > > data on the
>> > > > database, the path of the image could be on a table.
>> > > > How can I do that?
>> > > >
>> > > > --
>> > > > LUIS ESTEBAN VALENCIA
>> > > > MICROSOFT DCE 3.
>> > > > MIEMBRO ACTIVO DE ALIANZADEV
>> > > > http://spaces.msn.com/members/extremed/
>> > > >
>> > > >
>> > > >|||The notes on configuring the Unattended Execution Account are interesting,
and I can see how they would apply within a domain or intranet. Howeve, I
have a situation where my
customer is running a report on my Report Server and the photos to which the
URL's in the datafield point are on his/her secured server, which requires a
username and pwd to access?
Windows seems to store credentials for such access within user profiles
(e.g., get prompted for username and password when accessing a secure FTP
site and also get an opportunity to save the credentials). Can such
credentials be set once for the unattended report processing account for each
remote secured FTP/HTTP site that will contain photos that will be referenced?
-prophead
"Robert Bruckner [MSFT]" wrote:
> The SP1 readme explains why it "works" on SP1. It is something that was
> specifically added to SP1:
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images
> In particular, make sure you read the comment about configuring the
> UnattendedExecutionAccount in the readme notes.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "prophead" <prophead@.discussions.microsoft.com> wrote in message
> news:DC1075B2-4261-419D-9ABD-5ED96C5A0E2F@.microsoft.com...
> > Thanks, Neo. Installing SP1 fixed the problem. Evidently there was a
> > problem when you set the source to "External" and the Value to a field
> > from
> > the db with a URL. I also deleted the original report from the server and
> > re-deployed it, so that might have fixed it as well.
> >
> > -prophead
> >
> > "Neo" wrote:
> >
> >> Prophead,
> >>
> >> In my report, I have an image placed in the page header and the report
> >> text
> >> is placed in the body. I set the image value => >> ReportItems!txtImageLink.Value.
> >>
> >> What is the value of Fields.PhotgraphLocation.Value? It doesn't look
> >> like
> >> the field is being properly filled in. It should be something like
> >> "http://mysite/images/img.gif".
> >>
> >> BTW - SRC is the property of the Img html tag. Since the field is not
> >> being
> >> populated correctly, it will be "" or null.
> >>
> >>
> >> "prophead" wrote:
> >>
> >> > Neo,
> >> >
> >> > I tried what you suggested, but can't get it to work. I put
> >> > =ReportItems!txtPhotoLoc.Value in the Value property of the Image
> >> > control.
> >> > The Source property is set to External. txtPhotoLoc is the name of a
> >> > textbox
> >> > that has its value set to =Fields!PhotographLocation.Value.
> >> >
> >> > Is this what you did? It doesn't work for me in either the preview or
> >> > the
> >> > deployed version viewed through IE.
> >> >
> >> > Is there something special about the "ReportItems" qualifier? I can't
> >> > find
> >> > that object group anywhere in my project, but it doesn't complain about
> >> > it
> >> > when I save and deploy it.
> >> >
> >> > I checked and privileges are not a problem -- the content of
> >> > PhotographLocation it a fully qualified path to a URL you can paste in
> >> > any
> >> > browser and get an image (e.g.,
> >> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
> >> >
> >> > When I view source on the results in IE, the area that should show the
> >> > picture contains
> >> > <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
> >> > Why does SRC = ""?
> >> >
> >> > -prophead
> >> >
> >> > "Neo" wrote:
> >> >
> >> > > Hi Luis,
> >> > >
> >> > > I figured out a way to do it using Links. Create a textbox and point
> >> > > it to
> >> > > link stored in the dB. Create an image and under the image
> >> > > properties, set
> >> > > it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
> >> > >
> >> > > HTH
> >> > >
> >> > > "Luis Esteban Valencia" wrote:
> >> > >
> >> > > > I want to show a different image on my header depending on some
> >> > > > data on the
> >> > > > database, the path of the image could be on a table.
> >> > > > How can I do that?
> >> > > >
> >> > > > --
> >> > > > LUIS ESTEBAN VALENCIA
> >> > > > MICROSOFT DCE 3.
> >> > > > MIEMBRO ACTIVO DE ALIANZADEV
> >> > > > http://spaces.msn.com/members/extremed/
> >> > > >
> >> > > >
> >> > > >
>
>|||There is only one Unattended Execution Account per report server. This
account would be used for all remote secured sites.
The closest you can get to achieve this on RS 2000 is to write your own
custom assembly to retrieve the remotely secured images. Then you would have
full control over which credentials to use when accessing certain sites.
However, you no longer have the benefit of the credentials securely managed
and stored encrypted by the report server - you have to take care of that in
the custom assembly yourself.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"prophead" <prophead@.discussions.microsoft.com> wrote in message
news:A4A36A70-583A-401E-9D9C-80D93EE224AD@.microsoft.com...
> The notes on configuring the Unattended Execution Account are interesting,
> and I can see how they would apply within a domain or intranet. Howeve, I
> have a situation where my
> customer is running a report on my Report Server and the photos to which
> the
> URL's in the datafield point are on his/her secured server, which requires
> a
> username and pwd to access?
> Windows seems to store credentials for such access within user profiles
> (e.g., get prompted for username and password when accessing a secure FTP
> site and also get an opportunity to save the credentials). Can such
> credentials be set once for the unattended report processing account for
> each
> remote secured FTP/HTTP site that will contain photos that will be
> referenced?
> -prophead
>
> "Robert Bruckner [MSFT]" wrote:
>> The SP1 readme explains why it "works" on SP1. It is something that was
>> specifically added to SP1:
>> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images
>> In particular, make sure you read the comment about configuring the
>> UnattendedExecutionAccount in the readme notes.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "prophead" <prophead@.discussions.microsoft.com> wrote in message
>> news:DC1075B2-4261-419D-9ABD-5ED96C5A0E2F@.microsoft.com...
>> > Thanks, Neo. Installing SP1 fixed the problem. Evidently there was a
>> > problem when you set the source to "External" and the Value to a field
>> > from
>> > the db with a URL. I also deleted the original report from the server
>> > and
>> > re-deployed it, so that might have fixed it as well.
>> >
>> > -prophead
>> >
>> > "Neo" wrote:
>> >
>> >> Prophead,
>> >>
>> >> In my report, I have an image placed in the page header and the report
>> >> text
>> >> is placed in the body. I set the image value =>> >> ReportItems!txtImageLink.Value.
>> >>
>> >> What is the value of Fields.PhotgraphLocation.Value? It doesn't look
>> >> like
>> >> the field is being properly filled in. It should be something like
>> >> "http://mysite/images/img.gif".
>> >>
>> >> BTW - SRC is the property of the Img html tag. Since the field is not
>> >> being
>> >> populated correctly, it will be "" or null.
>> >>
>> >>
>> >> "prophead" wrote:
>> >>
>> >> > Neo,
>> >> >
>> >> > I tried what you suggested, but can't get it to work. I put
>> >> > =ReportItems!txtPhotoLoc.Value in the Value property of the Image
>> >> > control.
>> >> > The Source property is set to External. txtPhotoLoc is the name of
>> >> > a
>> >> > textbox
>> >> > that has its value set to =Fields!PhotographLocation.Value.
>> >> >
>> >> > Is this what you did? It doesn't work for me in either the preview
>> >> > or
>> >> > the
>> >> > deployed version viewed through IE.
>> >> >
>> >> > Is there something special about the "ReportItems" qualifier? I
>> >> > can't
>> >> > find
>> >> > that object group anywhere in my project, but it doesn't complain
>> >> > about
>> >> > it
>> >> > when I save and deploy it.
>> >> >
>> >> > I checked and privileges are not a problem -- the content of
>> >> > PhotographLocation it a fully qualified path to a URL you can paste
>> >> > in
>> >> > any
>> >> > browser and get an image (e.g.,
>> >> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
>> >> >
>> >> > When I view source on the results in IE, the area that should show
>> >> > the
>> >> > picture contains
>> >> > <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
>> >> > Why does SRC = ""?
>> >> >
>> >> > -prophead
>> >> >
>> >> > "Neo" wrote:
>> >> >
>> >> > > Hi Luis,
>> >> > >
>> >> > > I figured out a way to do it using Links. Create a textbox and
>> >> > > point
>> >> > > it to
>> >> > > link stored in the dB. Create an image and under the image
>> >> > > properties, set
>> >> > > it's value to that textbox (e.g.,
>> >> > > =ReportItems.txtImageLink.Value).
>> >> > >
>> >> > > HTH
>> >> > >
>> >> > > "Luis Esteban Valencia" wrote:
>> >> > >
>> >> > > > I want to show a different image on my header depending on some
>> >> > > > data on the
>> >> > > > database, the path of the image could be on a table.
>> >> > > > How can I do that?
>> >> > > >
>> >> > > > --
>> >> > > > LUIS ESTEBAN VALENCIA
>> >> > > > MICROSOFT DCE 3.
>> >> > > > MIEMBRO ACTIVO DE ALIANZADEV
>> >> > > > http://spaces.msn.com/members/extremed/
>> >> > > >
>> >> > > >
>> >> > > >
>>