Trouble with convert(xml) in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trouble with convert(xml) in query

Author

Topic

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2010-08-01 : 16:12:36

Hello All:

I am attempting to query a database with a datatype of ntext. The values are in an XML format.

There is something wrong with my syntax, but I can't figure out the xml piece that is missing.

SELECT STATEMENT:
SELECT
convert(xml,ChangesXml).value('/AuditTrail/ChangesXml/Tasks/Task/WorkflowStepName [1]','varchar(max)')
as OriginalWorkflowStepName
from audittrails

The error I receive is:

Msg 2389, Level 16, State 1, Line 2
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

SAMPLE OF field value for changesxml:

<AuditTrail><AuditType>1</AuditType><ItemType>2</ItemType><AuditDateTime>2010-08-01T11:50:35</AuditDateTime><AuditSource>0</AuditSource><UserName>Adminl</UserName><UserLogin>Admin</UserLogin><ChangesXml><Tasks> <Task> <ActualMinutes>1060</ActualMinutes> <ActualDuration>17.6667 hrs</ActualDuration> <LastUpdatedDateTime>2010-06-24T14:37:52.13-04:00</LastUpdatedDateTime> <Custom_178>Integration Issues</Custom_178> </Task> <Task> <ActualMinutes>1060.2</ActualMinutes> <ActualDuration>17.67 hrs</ActualDuration> <LastUpdatedDateTime>2010-08-01T11:50:35.067-04:00</LastUpdatedDateTime> Integration Issues,Additional Functionality Requests From Customer</Custom_178> </Task> </Tasks></ChangesXml></AuditTrail>

What I am attempting to do is pull out the LastUpdatedDateTime and the value in the <Custom_178> tag.

Any help is greatly appreciated!

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-08-02 : 02:43:41

Hi,

your input xml is not in correct format
i changed it

declare @x xml
set @x='
<AuditTrail>
<AuditType>1</AuditType>
<ItemType>2</ItemType>
<AuditDateTime>2010-08-01T11:50:35</AuditDateTime>
<AuditSource>0</AuditSource>
<UserName>Adminl</UserName>
<UserLogin>Admin</UserLogin>
<ChangesXml>
<Tasks>
<Task>
<ActualMinutes>1060</ActualMinutes>
<ActualDuration>17.6667 hrs</ActualDuration>
<LastUpdatedDateTime>2010-06-24T14:37:52.13-04:00</LastUpdatedDateTime>
<Custom_178>Integration Issues</Custom_178>
</Task>
<Task>
<ActualMinutes>1060.2</ActualMinutes>
<ActualDuration>17.67 hrs</ActualDuration>
<LastUpdatedDateTime>2010-08-01T11:50:35.067-04:00</LastUpdatedDateTime>
<Custom_178>Integration Issues,Additional Functionality Requests From Customer</Custom_178>
</Task>
</Tasks>
</ChangesXml>
</AuditTrail>
'
select @x.value('(//AuditTrail/ChangesXml/Tasks/Task/WorkflowStepName)[1]','varchar(max)')

Iam a slow walker but i never walk back

Sachin.Nand

2937 Posts

Posted - 2010-08-02 : 08:48:34

[code]
declare @x xml
set @x='
<AuditTrail>
<AuditType>1</AuditType>
<ItemType>2</ItemType>
<AuditDateTime>2010-08-01T11:50:35</AuditDateTime>
<AuditSource>0</AuditSource>
<UserName>Adminl</UserName>
<UserLogin>Admin</UserLogin>
<ChangesXml>
<Tasks>
<Task>
<ActualMinutes>1060</ActualMinutes>
<ActualDuration>17.6667 hrs</ActualDuration>
<LastUpdatedDateTime>2010-06-24T14:37:52.13-04:00</LastUpdatedDateTime>
<Custom_178>Integration Issues</Custom_178>
</Task>
<Task>
<ActualMinutes>1060.2</ActualMinutes>
<ActualDuration>17.67 hrs</ActualDuration>
<LastUpdatedDateTime>2010-08-01T11:50:35.067-04:00</LastUpdatedDateTime>
<Custom_178>Integration Issues,Additional Functionality Requests From Customer</Custom_178>
</Task>
</Tasks>
</ChangesXml>
</AuditTrail>
'
select @x
select x.i.value('ActualMinutes[1]','varchar(max)'),
x.i.value('LastUpdatedDateTime[1]','varchar(max)'),
x.i.value('Custom_178[1]','varchar(max)')
from @x.nodes('/AuditTrail/ChangesXml/Tasks/Task')x(i)

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources