Back devices: store

Showing posts with label store. Show all posts

Wednesday, March 28, 2012

How can I query the node in XML with XQuery?

1. I store the xml info below in the XML field Demographics in SQL 2005, I hope to query all the node info with XQuery.

The result just like
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>

but I can't get the correct result using
select Demographics.query('/Folder') from store

2. Furthermore, How can I query the Name attribute of the Folder node by ID of Folder using XQuery,
If so, I can get the Name (such as "Root") after I know the ID "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"

3. Can I get the result below using XQuery? (Notice: the sub Name="Card" have no content )

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
</Bookmark>

</Folder>

Could you help me? Thanks!

=========================XML Info=====================================

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark>
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark>
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>
</Folder>

======================XML Info=====================================

Before I get to the questions: There are many examples in our MSDN whitepapers. Two in particular will be very informative -

1) XML feature overview: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
2) XQuery: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_xqueryintro.asp

Now for your questions:

1) I ran your code and got back the expected result - the full XML. What was your expection?

2) Try
SELECT Demographics.value ('(//Folder[@.Id = "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"]/@.Name)[1]', 'nvarchar(64)')
FROM store

When your table contains more than one row, you will get a NULL value from the rows that do not contain a <Folder> with Id "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d". Eliminate those rows using the exist() method in a (T-SQL) WHERE clause or write an outer SELECT statement. There are examples in the whitepapers above.

3) Here is one way of writing your query:
SELECT Demographics.query ('
for $f in /Folder
return
<Folder Name="{$f/@.Name}" Id="{$f/@.Id}">
{for $b in $f/Bookmark
return <Bookmark> {$b/Title}{$b/Url}</Bookmark>}
<Folder Name="{$f/Folder/@.Name}" Id="{$f/Folder/@.Id}">
{$f/Folder [@.Name ne "Card"]/Bookmark}
</Folder>
</Folder>')
FROM store

Not sure what you are trying to do - if you can tell us more, we might be able to suggest other solutions.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||

When I run "select Demographics.query('/Folder') from store", I get the Result 1, but what I expect is Result 2 ! How can I get Result 2 ？

//-- Result 1 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark>
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark>
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>
//--

//-- Result 2 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>
//

|||

Your expectation is not inline with what the XPath or XQuery specification defines should be the result. The children of the folder node are part of that node, and thus they are returned. You can get the results you are looking for with an XQuery statement like this:

select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')

However, this will only work for "Folder" elements which are nested at two levels deep. You will find difficulty in supporting more generic scenarios since user defined functions are not supported in our XQuery implementation.

Another option would be to use our DML language to remove elements which are not named "Folder":

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x

This works by removing all of the nodes which are not named "Folder", along with any text content.

-John

|||

Thank you very much!

The following code you wrote is not OK
select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')
The following code is OK!!!

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x
but what does the "local-name" mean?

//--Code-
declare @.my xml

set @.my='
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark BId="f8dce8hj-846c-4e38-ab2f-6d03d9711b80">
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark BId="fgdce3ak-846c-4e38-ab2f-8i03d9711b23">
<Title>We ll e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-11-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>

<Folder Name="Card1" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark BId="ghdce3ak-456c-4e38-ab2f-5h02d9711b67">
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-12</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark BId="fkdfh3a8-456c-6y38-jk2f-5h0gh9711b45">
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-25</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>

<Folder Name="Card1in1" Id="l9dcf8fe-689c-0935-fghj-7u03d9711b5t">
<Bookmark BId="ghdfh3a8-896c-6y40-jkfg-5h0gh9711b89">
<Title>The News of CNN</Title>
<Url>https://www.cnn.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-23</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>

</Folder>

</Folder>
'

set @.my.modify('delete (//*[local-name(.) != "Folder"], //text()) ')

select @.my

//--Code-

|||local-name returns the "local name" of a node. All element names are made up of two parts (this is what is called a qualified name, or QName): a) the namespace uri, and b) the local name. The namespace uri is usually indicated by a prefix which is bound to the actual namespace uri. For example, take this node:

<a:foo xmlns:a="bar" />. It has "bar" as its namespace uri, and "foo" as its local name.

So what the query does that I provided is it checks every element in the document, and if the local name does not equal "Folder", it deletes it from the document.

How can I query the node in XML with XQuery?

1. I store the xml info below in the XML field Demographics in SQL 2005, I hope to query all the node info with XQuery.

The result just like
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>

but I can't get the correct result using
select Demographics.query('/Folder') from store

3. Can I get the result below using XQuery? (Notice: the sub Name="Card" have no content )

</Folder>

Could you help me? Thanks!

=========================XML Info=====================================

======================XML Info=====================================

Before I get to the questions: There are many examples in our MSDN whitepapers. Two in particular will be very informative -

Now for your questions:

1) I ran your code and got back the expected result - the full XML. What was your expection?

2) Try
SELECT Demographics.value ('(//Folder[@.Id = "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"]/@.Name)[1]', 'nvarchar(64)')
FROM store

Not sure what you are trying to do - if you can tell us more, we might be able to suggest other solutions.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||

When I run "select Demographics.query('/Folder') from store", I get the Result 1, but what I expect is Result 2 ! How can I get Result 2 ？

//-- Result 2 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>
//

|||

select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')

Another option would be to use our DML language to remove elements which are not named "Folder":

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x

This works by removing all of the nodes which are not named "Folder", along with any text content.

-John

|||

Thank you very much!

The following code you wrote is not OK
select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')
The following code is OK!!!

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x
but what does the "local-name" mean?

//--Code-
declare @.my xml

</Folder>

</Folder>
'

set @.my.modify('delete (//*[local-name(.) != "Folder"], //text()) ')

select @.my

//--Code-

<a:foo xmlns:a="bar" />. It has "bar" as its namespace uri, and "foo" as its local name.

So what the query does that I provided is it checks every element in the document, and if the local name does not equal "Folder", it deletes it from the document.

Monday, March 26, 2012

How can I query the appointed node in XML with XQuery quickly?

I store the xml info below in the XML field in SQL 2005, I hope to query all the Folder node with XQuery.

The result I hope to get

================ Stored In XML field==========================

=====================================================

Some pepole give me a anwser, It work well, but when the size of the XML field is little big, it run very slowly, could you give a code which can run quickly!

//--Code -
declare @.my xml

</Folder>

</Folder>
'

set @.my.modify('delete (//*[local-name(.) != "Folder"], //text()) ')

select @.my

//--Code-

That ususally is done using recursion. But provided SQL Server doesn't support user-defined functions in XQuery it seems to be unfeasible to implement. Another workaround would be using XSLT.

Are you sure you need such kind of filtering? You said you only need to query some node, not filtering XML tree.

|||

Another way to write it would be to use the nodes() method to generate a relational rowset that contains Name, Id and ParentID (see the other posting for code samples), then write a recursive relational user-defined function that recomposes the hierarchy (see the FOR XML whitepaper for a code sample).

However, what I assume in your case, is that the logging of the changes is slowing you down. Have you checked what your data and IO disk load is? Do you have your log file on a different disk drive/disk spindle than your data?

Best regards

Michael

how can i process multiple binary files into my sql database?

i have a table with rows of file names and paths. what i'm trying to do is process each file and store it in my sql database. i want to store the files as binary files (they are word and excel and pdf files) anyone know a way to do this? it would especially be useful if i could do this with a console application so i can schedule it

There are lots of examples which shows how to upload/retrieve BLOB data in SQL. For example:

HOW TO: Read and Write BLOB Data by Using ADO.NET Through ASP.NEThttp://support.microsoft.com/default.aspx?scid=kb;en-us;326502

You can also use OPENROWSET command with SINGLE_BLOB option in SQL2005. Please refer to:

http://msdn2.microsoft.com/en-us/library/ms190312.aspx

And you can go through the tale to retrieve the file names/paths to locate the source files, using SqlDataReader:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(d=ide).aspx

|||

thank you!

that was exactly what i've been looking for. i grabbed the code and it works beautifully.

you saved my butt

threeo

Friday, March 23, 2012

how can I post back a statement from a store procedure to the .aspx page

Hi all,
Anyone can show me how can I catch the 'Print' statement that I have defined in my store procedure using SQL server 2000 DB on the .aspx page? ( I am using ASP.NET 1.0)
My store procedure as follow:
CREATE PROC NewAcctType
(@.acctType VARCHAR(20))
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
PRINT 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
PRINT 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
Thanks for all your replies

The best thing to do would be to either create another parameter and set its type to output or return the statement as a select.
@.message varchar(100) output
set @.message = 'The account type is already exists'
or
select 'The account type is already exist' as message

Nick

|||Hi Nick,
As you stated:
@.message varchar(100) output
set @.message = 'The account type is already exists'
Do I put a return statement like "Return @.message"?
How about if there is no errror in the procedure, do I still need to return any value to the .aspx page?
Thanks.|||Hi Nick,
when I executed my store procedure in SQL 2000 server, I got an error said"Cannot use the OUTPUT option in a DECLARE statement"
But without the Declare keyword, I got an incorrect syntax error, so how can I solve this problem? Is that necessary to put the "output" keyword at the end of the declare varaible statement?
Thanks|||syntax is:
create procedure whateverName
@.message varchar(100) output
as
set @.message = ''
if @.@.ERROR
set @.message = 'your text here.'
If you have no error, the top set statement will allow a blank to be passed back.
Nick|||

Nick,
Here is my syntax:
CREATE PROC DeleteCust
(@.SSN VARCHAR(12), @.message VARCHAR(40) output)
AS
BEGIN

--checks if the SSN is already exist
IF NOT EXISTS (SELECT * FROM Customer WHERE SSN = @.SSN)
BEGIN
SET @.message = 'The SSN is not exist!'
RETURN
END
BEGIN TRANSACTION
DELETE FROM Customer WHERE SSN = @.SSN

--if there is an error on the delete, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Delete failure on Customer table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END

I executed this proc as:
declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
Given that SSN is invalid, I suppose got the message 'The SSN is not exist!", however, I didn't get that message from the execution instead the system message showed "The command(s) completed successfully.", so anywhere I was wrong with the above SP or the execute statement?
Appreciated your reply

|||

If your just looking for the value after a run in QA, add:

declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
select @.message
Nick

|||

Thanks nick. I got the message when I run the Store Procedure in SQL server. However, how can I get the error message when I called the Store Procedure on my .aspx page?
I have these codes on my page: ('DeleteCust' is my store procedure name, 'SSN.Text' is the value from the input box)
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));

var myCommand : SqlDataAdapter = new SqlDataAdapter("DeleteCust", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.SSN", SqlDbType.VarChar, 12)).Value = SSN.Text;
Then, what should I put it here to catch the @.message value? The @.message is VARCHAR, and I have a Return keyword within my procedure, and the return value type is INT?
ping

|||

Add (Code is in VB):

dim parm as new sqlParameter("@.message", sqlDBtype.varchar, 100)

parm.direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parm)
After you do your call to stored proc:
strMessage(Or whatever variable you are adding to) = myCommand.SelectCommand.Parameters(1).Value
Nick

|||Hi Nick,
Could u explain more detail for these statements coz I am new to doing asp, I want to know it more about the meaning of those codes.
parm.direction = ParameterDirection.Output
strMessage(what variable should I add it here? could u give me an example? are u talking about @.message?)
when do I use strMessage() ?
Many thanks.

|||

Here is a quick and dirty article on output parms.

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=624

Nick

Wednesday, March 28, 2012

How can I query the node in XML with XQuery?

How can I query the node in XML with XQuery?

Monday, March 26, 2012

How can I query the appointed node in XML with XQuery quickly?

how can i process multiple binary files into my sql database?

Friday, March 23, 2012

how can I post back a statement from a store procedure to the .aspx page

Back devices

Blog Archive

About Me