Opening local XML file

Good evening
I want to open a local XML file from my SQL script.
#1. If you are in some dev't environment such as .Net or script, you can
open the file and pass it into the SQL script. But I want that within SQL
script.
#2. You can write a CLR function to open it. I did. However I feel like pay
too much for such a trivial job.
#3. I found a way to retrieve a file as base64 string. e.g.,
declare @.b varbinary(max)
set @.b=(select * from openrowset(bulk N'c:\x.xml', single_blob) as b)
That looks like decent base64 binary text which can be converted into XML
stream easily, if you are in .Net. But how do you do that within SQL script?
Or is there another way #4, #5, ... I have not imagined?
#2 UDF
public static SqlXml getdoc(string xmlPath)
{
XmlReader xr = XmlReader.Create(xmlPath);
SqlXml retSqlXml = new SqlXml(xr);
xr.Close();
return (retSqlXml);
}
Pohwan Han. Seoul. Have a nice day.declare @.b xml
set @.b=(select * from openrowset(bulk N'c:\x.xml', single_blob) as b)
SELECT @.b
VARBINARY can be implicitly converted to xml, assuming the stream actually
is xml.
Dan

> declare @.b varbinary(max)
> set @.b=(select * from openrowset(bulk N'c:\x.xml', single_blob) as b)|||Thanks Dan.
It worked.
Pohwan Han. Seoul. Have a nice day.
"Dan Sullivan" <danATpluralsight.com> wrote in message
news:964a9ae6140488c868bbaa3f4180@.news.microsoft.com...
> declare @.b xml
> set @.b=(select * from openrowset(bulk N'c:\x.xml', single_blob) as b)
> SELECT @.b
> VARBINARY can be implicitly converted to xml, assuming the stream actually
> is xml.
> Dan
>
>sql