Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl

trigger to view it in result set.

I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for

ddl_database_level_events
as
--select

eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post

Time'
from

@.data.nodes('/EVENT_INSTANCE')

as EventsTable(Col)
go

Thank you in advance,

Bishoy

The path expressions in the value methods need to be relative path expressions from the node...

Try:

select
Col.value('(EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)
go

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl

trigger to view it in result set.

I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for

ddl_database_level_events
as
--select

eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post

Time'
from

@.data.nodes('/EVENT_INSTANCE')

as EventsTable(Col)
go

Thank you in advance,

Bishoy

Your XPATH expression for the value method is incorrect. You can do one of the following:

select
Col.value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)

-- or

select
Col.value('(./EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(./PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)

See Books Online EVENTDATA topics for more examples. And check out the XQuery documentation also.

sql

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl trigger to view it in result set.

I made that code but it returned null, any help please?

createtrigger DatabaseEvents
ondatabase
for ddl_database_level_events
as
--select eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)')as'Event Type'
,Col.value('(/PostTime/text())[1]','datetime')as'Post Time'
from @.data.nodes('/EVENT_INSTANCE')as EventsTable(Col)
go

Thank you in advance,

Bishoy

The path expressions in the value methods need to be relative path expressions from the node...

Try:

select
Col.value('(EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)
go

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes severa
l
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End FunctionI don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>sql

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes several
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End Function
I don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>

Question about Querying by "for xml auto" and retriving xml by

Thanks very much for reply. Actually, I trid a same page (same result of
query) on two boxes. It worked for one but failed on another. Is it possible
that different system handles NULL value differently?
Ruopian
"Jay Nathan" wrote:

> I don't know what line that this is failing on, but if I had to guess, I
> think it would be in this area:
>
> cart.Attributes(0).Value.ToString & "</cart>"
> You probably have some NULL values and therefore aren't bringing back the
> attribute value that you are trying to retrive here:
> "cart.Attributes(0).Value". I would check the results of the query first.
> Jay Nathan
> http://www.jaynathan.com/blog
>
>
> "Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
> news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> several
> and
> auto"
> result.
> cart.Attributes(0).Value.ToString
>
>Are both of the boxes connecting to the same data source (not to be too
simple minded, here...)? If so, you could have a NULL in one db and not the
other. The functionality of the DOM shouldn't be any different from machine
to machine.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:2A522413-E03D-48FD-A14A-DF1255FFBB12@.microsoft.com...
> Thanks very much for reply. Actually, I trid a same page (same result of
> query) on two boxes. It worked for one but failed on another. Is it
possible
> that different system handles NULL value differently?
> Ruopian
> "Jay Nathan" wrote:
>
the
first.
an
2003,
pwd=pwd;
xml|||yes, they are connecting to the same database. Actually, they are two server
s
of a cluster. Can you explaine why I could have a NULL in one server and not
the other by querying the same db? Thanks a lot!
Ruopian
"Jay Nathan" wrote:

> Are both of the boxes connecting to the same data source (not to be too
> simple minded, here...)? If so, you could have a NULL in one db and not th
e
> other. The functionality of the DOM shouldn't be any different from machin
e
> to machine.
> --
> Jay Nathan
> http://www.jaynathan.com/blog
>
> "Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
> news:2A522413-E03D-48FD-A14A-DF1255FFBB12@.microsoft.com...
> possible
> the
> first.
> an
> 2003,
> pwd=pwd;
> xml
>
>|||Actually, if you were hitting the same db with the same query, then you
shouldn't have different values. Have you tried to run the queries by
returning a regular resultset, not in XML format? On either machine? My gut
is that you have a NULL value coming back somewhere, you just need to find
it, or handle it gracefully as you are looping through the DOM.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:4002B56E-9884-488F-B055-8F8B7D377892@.microsoft.com...
> yes, they are connecting to the same database. Actually, they are two
servers
> of a cluster. Can you explaine why I could have a NULL in one server and
not
> the other by querying the same db? Thanks a lot!
> Ruopian
> "Jay Nathan" wrote:
>
the
machine
of
guess, I
back
crashes
of
for
the