Hello
I try import data from XML file to MSSQL2005 using XQUERY
xml like:

How do I join 3 tables to get the following xml structure:

I am having trouble querying a column in my sql server database that
contains the following xml

http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xsi:type="CarData">12322 LeaseCars>5

I have tried the following,

BaseData.value('/CarData/RentalCars', '[1]','int');

and I get simply a 0 returned when I know there is actually a value
there. I have also tried,

BaseData.query('RentalCars');

and I get 123

I want to try and read all of the values contained in this one column,
but nothing I have tried so far seems to work So any help would be
appreicated.

I just installed SQL Server 2005 Express Advanced, which has reporting
services. IIS is installed. I'm configuring the Report Manager Virtual
Directory Settings part of the "Configure Report Server". This is a 2 part
question:

1. After configuring the Report Manager Virtual Directory, the icon remains
an X, rather than changing into a checkmark, even though "settings have been
applied".

2. When I test out the fully configured Report Server by opening Explorer
and entering http://localhost/ReportServer/ I get a "The XML page cannot be
displayed", "Cannot view XML input using XSL style sheet", rather than the
Home Page of the SQL Server Reporting Services. (It's taken me months to
get to this point, including the purchase of XP discs to get IIS running.)

I want to extract the value of the CorrelationID without having to specify
the entire path. There will be over 50 different XML documents with
this attribute and I would like to avoid a large number of statements to get
this value.

declare @MessageBody xml
,@CorrelationID bigint

select @MessageBody = ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" PartnerID="2"
CorrelationID="123" />'

--get correlationID for message
select @CorrelationID = @MessageBody.value('for $p in /BreakTypeRequestMsg[1]
return
$p/@CorrelationID[1]','bigint')

select @CorrelationID

In SQL server 2005, distributed query is not supported if there is xml
type column in the table. I could use nvarchar in the table and create
view and cast it to xml to work around it. But how could I utilize the
xml schema? How to utilize XML index in such situation? Please help.
Thanks a lot.

George

I'm having issues importing XML data using a distributed query with
the following statement which is run from an XP workstation named A
connecting to SQL2005 Server B, the data is located on a server C at
the following UNC path \\SERERC\SHARE\PATH\DATAFILE.XML

DECLARE @xml XML
SELECT @xml =CONVERT(XML, bulkcolumn, 2)
FROM OPENROWSET(BULK '\\SERVERC\SHARE\PATH\DATAFILE.XML', SINGLE_BLOB)
AS x
SELECT @xml

Results:
Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "\\ServerC\Share\Path\DataFile.xml"
could not be opened. Operating system error code 5(Access Denied).

The SQL Server service is running using a domain user account with
permissions to read the remote files. I have logged in locally to the
SQL server and verified this. Also, if I run the query from the SQL
Server B, the query is successful.

In SQL Server, how would you extract:




from a sql column of type xml with value:








and insert (the above extraction) into another sql column of type xml
with current value:






for a result of:

I need to query an XML string in SQL Server, and pull a couple of
elements as columns in the query result. read an excellent article on
the subject of xquery (http://weblogs.asp.net/jgalloway/archive/
2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx).
I was able to get this to work after some work, but can't figure out
how to query two fields. I've tried several different things to get
the second column to show, and can only get the first one to show.
Currently, my code looks like this:


DECLARE @PartData xml
set @PartData = (SELECT [PartData]
FROM [table1].[dbo].[AppParts]
where Appid = 272)

-- needed the following to strip out XML namespace, or query returns
nothing
set @PartData = Replace(cast(@PartData as varchar(5000)), 'http://
temp.org/D.xsd', '')

Here is the script:

create table test(id int identity(1,1), doc varchar(max))
create view dbo.[doc]
as
select id,
col.value('(./last_name)[1]','varchar(200)') as [Last Name],
col.value('(./first_name)[1]','varchar(200)') as [First Name]
from [test] u
cross apply (cast (doc as xml)).nodes('people/person') as t(col)

Doc is in xml format. There is error:Incorrect syntax near the keyword
'as'.