CHARINDEX doesn't work - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

CHARINDEX doesn't work

Author

Topic

barb0sul
Starting Member

14 Posts

Posted - 2012-03-21 : 07:09:47

I have XML code in a column of a table.
I try to search by a substring using CHARINDEX but it seems it doesn't work.
The expression I need to search is
"<HOOName>Name Surname</HOOName> <IsImpacted>false</IsImpacted>"

Can anyone help me ? The result of CHARINDEX(expression, column) is always 0 which is false because I can find this text within the column value.

Thanks,
barb0sul

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-21 : 07:29:54

This may be due to white spaces, carriage returns etc. in the XML data. SQL Server does not store an exact character for character string of the XML (it stores a XML Documentation Markup). So it is not even guaranteed that when you retrieve an XML column and convert to string representation, it would be identical to the string you initially used to send the data to the XML column.

If you need to search for nodes or attributes in an XML column, it is far better and perhaps more efficient write xqueries. If you post a sample of the XML, people on this forum would be able to suggest ways of querying.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-21 : 07:33:55

The use of XML columns are case sensitive.

N 56°04'39.26"
E 12°55'05.63"

barb0sul
Starting Member

14 Posts

Posted - 2012-03-21 : 07:36:39

The entire cell value is a repetition of

<HOO> <HOOId>a2791398-c673-407d-9e4a-51b37d159b21</HOOId> <HOOName>Name Surname</HOOName> <IsImpacted>false</IsImpacted> <Comment>No impact.</Comment> <IsApproved>true</IsApproved> </HOO> ending with CR LF.

At the begining I have <ITSystemsResourcesDS xmlns="http://tempuri.org/ITSystemsResourcesDS.xsd">

I'm not familiar with xml and I have limited access to the database (read only).

Regards,
Barb0sul.

barb0sul
Starting Member

14 Posts

Posted - 2012-03-21 : 07:41:06

quote:
Originally posted by SwePeso

The use of XML columns are case sensitive.

N 56°04'39.26"
E 12°55'05.63"

I have already tried with lower() both for search string and searched field, it doesn't work.

Any other ideas ?

Regards,
Barb0sul.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-21 : 07:43:54

What do you want to search for?
What do you want to display?

N 56°04'39.26"
E 12°55'05.63"

barb0sul
Starting Member

14 Posts

Posted - 2012-03-21 : 07:47:08

quote:
Originally posted by SwePeso

What do you want to search for?
What do you want to display?

N 56°04'39.26"
E 12°55'05.63"

I want to put a condition in my query to extract only the records that contains <HOOName>Name Surname</HOOName> <IsImpacted>false</IsImpacted>, the condition I tried is CHARINDEX('<HOOName>Name Surname</HOOName> <IsImpacted>false</IsImpacted>', column)>0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-21 : 07:56:37

[code]DECLARE @Data XML = '<HOO> <HOOId>a2791398-c673-407d-9e4a-51b37d159b21</HOOId> <HOOName>Name Surname</HOOName> <IsImpacted>false</IsImpacted> <Comment>No impact.</Comment> <IsApproved>true</IsApproved> </HOO>'

SELECT n.value('HOOName[1]', 'VARCHAR(MAX)'),
n.value('IsImpacted[1]', 'BIT')
FROM @Data.nodes('/HOO') AS f(n)
WHERE n.value('HOOName[1]', 'VARCHAR(MAX)') = 'Name Surname'
AND n.value('IsImpacted[1]', 'BIT') = 0[/code]

N 56°04'39.26"
E 12°55'05.63"

barb0sul
Starting Member

14 Posts

Posted - 2012-03-21 : 08:17:35

quote:

Originally posted by SwePeso

DECLARE	@Data XML = '<HOO> <HOOId>a2791398-c673-407d-9e4a-51b37d159b21</HOOId> <HOOName>Name Surname</HOOName> <IsImpacted>false</IsImpacted> <Comment>No impact.</Comment> <IsApproved>true</IsApproved> </HOO>'

SELECT	n.value('HOOName[1]', 'VARCHAR(MAX)'),
	n.value('IsImpacted[1]', 'BIT')
FROM	@Data.nodes('/HOO') AS f(n)
WHERE	n.value('HOOName[1]', 'VARCHAR(MAX)') = 'Name Surname'
	AND n.value('IsImpacted[1]', 'BIT') = 0

N 56°04'39.26"
E 12°55'05.63"

Any help on how do I include this in my query ?

SELECT *
FROM [ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView] a
where a.ProjectActualStartDate between '26 may 2011' and '19 jan 2012'
and a.[PMS Project Status] between 15 and 18
and (a.[PMS IT Systems And Resources] is not null or a.[PMS IT Systems And Resources] <> '')

The condition must be attached to this query ...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-21 : 09:11:10

You can add to your where clause like this:

SELECT *
FROM [ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView] a 
where a.ProjectActualStartDate between '26 may 2011' and '19 jan 2012'
and a.[PMS Project Status] between 15 and 18
and (a.[PMS IT Systems And Resources] is not null or a.[PMS IT Systems And Resources] <> '')
AND CAST(xmlCol.query('data(/HOO/HOOName)') AS VARCHAR(32)) = 'Name Surname'
AND CAST(xmlCol.query('data(/HOO/IsImpacted)')AS VARCHAR(32)) = 'false'

Edit: On second thought, it might be more efficient and simpler to use the exist function like this. Keep in mind though, that this would be case sensitive:

ANDxmlCol.exist('/HOO/HOOName[fn:contains(.,"Name Surname")]') = 1
AND xmlCol.exist('/HOO/IsImpacted[fn:contains(.,"false")]') = 1

barb0sul
Starting Member

14 Posts

Posted - 2012-03-21 : 09:24:23

Can you help me with declarations also ?
I tried your way and I receive an error stating that I cannot call methods on nvarchar.
The column PMS IT Systems And Resources is nvarchar(4000).
The content of the column is not the same ... one HOO can be found at the begining for the first row and at the end for the next one.
How should I declare all variables ?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 09:42:47

"CHARINDEX Doesn't work"

You funny

AND..instead of going around in Circles...how about YOU post some sample Data and what you expect as a return..also the DDL

Why are you trying to interrogate XML in the first place?

Just like SSIS, BI, DTS, what have you...if everything looks like a nail

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

barb0sul
Starting Member

14 Posts

Posted - 2012-03-21 : 10:15:38

For X002548:

As I said before, I have limited access to the database, I cannot extract the DDL. Furthermore, I actualy made a mistake, it's not a table, it's a view. The column containing XML code is defined as nvarchar(4000).
In this field, I'm interested in finding the records that contains a certain HOO with IsImpacted=True.

The query I wrote so far is:

SELECT *
FROM [ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView] a
where a.ProjectActualStartDate between '26 may 2011' and '19 jan 2012'
and a.[PMS Project Status] between 15 and 18
and (a.[PMS IT Systems And Resources] is not null or a.[PMS IT Systems And Resources] <> '')
... and this must be continued with the HOO condition.

An example of data in that column (remember the position you find a certain HOO isn't always the same, neither the content of some properties):
<ITSystemsResourcesDS xmlns="http://tempuri.org/ITSystemsResourcesDS.xsd"> <HOO> <HOOId>b94ca519-e6f7-4f3d-ab41-3db5fc4faf84</HOOId> <HOOName>John Doe</HOOName> <IsImpacted>false</IsImpacted> <Comment>No impact in CRM systems.</Comment> <IsApproved>true</IsApproved> </HOO> <HOO> <HOOId>54717ab5-5a89-44b8-b408-7776b468f4ea</HOOId> <HOOName>Diana Monroe</HOOName> <IsImpacted>false</IsImpacted> <Comment>No impact.</Comment> <IsApproved>true</IsApproved> </HOO> <HOO> <HOOId>8a9ddc4d-89e4-4886-97e1-6c016bccb614</HOOId> <ITSystems> <Id>1</Id> <HOOId>ebf07f56-4fd3-4999-8c32-abbb33ce927e</HOOId> <ITSystemId>1abcaba8-2ac7-44b7-ac8e-e0e1abad2ecb</ITSystemId> <ITSystemName>EMM</ITSystemName> <Resources> <Id>1</Id> <ResourceId>ebf07f56-4fd3-4999-8c32-abbb33ce927e</ResourceId> <ResourceName>Steve McCalister</ResourceName> <StartDate>2011-11-21T00:00:00+02:00</StartDate> <EndDate>2011-11-24T00:00:00+02:00</EndDate> </Resources> </ITSystems> </HOO> </ITSystemsResourcesDS>

If I missed something please let me know ...
Thank you very much for your help.

Regards.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-21 : 11:16:09

quote:
Originally posted by barb0sul

Can you help me with declarations also ?
I tried your way and I receive an error stating that I cannot call methods on nvarchar.
The column PMS IT Systems And Resources is nvarchar(4000).
The content of the column is not the same ... one HOO can be found at the begining for the first row and at the end for the next one.
How should I declare all variables ?

I thought the data type of the column was XML. If it is nvarchar, you should cast it to XML and then call the xquery functions on it like this:

AND CAST(xmlCol AS XML).exist('/HOO/HOOName[fn:contains(.,"Name Surname")]') = 1
AND CAST(xmlCol AS XML).exist('/HOO/IsImpacted[fn:contains(.,"false")]') = 1

However, my experience has been that more often than not, data that is allegedly XML that is stored in nvarchar columns turn out to be XML that is not well-formed. If that is the case, this might not work.

barb0sul
Starting Member

14 Posts

Posted - 2012-03-22 : 04:44:43

Here's one concatenated entry from table:

<?xml version="1.0" encoding="UTF-8"?>
<ITSystemsResourcesDS xmlns="http://tempuri.org/ITSystemsResourcesDS.xsd">
<HOO>
<HOOId>4917ef06-6e19-477c-bd35-97cf58fdfad2</HOOId>
<HOOName>George Popovici</HOOName>
<IsImpacted>true</IsImpacted>
<Comment>Details about ticklers and other info if needed</Comment>
<IsApproved>true</IsApproved>
<ITSystems>
<Id>1</Id>
<HOOId>4917ef06-6e19-477c-bd35-97cf58fdfad2</HOOId>
<ITSystemId>d6670f6b-48c8-4117-b5cf-3aaad2da05f5</ITSystemId>
<ITSystemName>BSCS</ITSystemName>
<Resources>
<Id>1</Id>
<ResourceId>2f13ce71-9088-434b-8fbb-3b94142116ec</ResourceId>
<ResourceName>Mikael Harnshaw</ResourceName>
<StartDate>2011-07-18T00:00:00+03:00</StartDate>
<EndDate>2011-07-22T00:00:00+03:00</EndDate>
</Resources>
</ITSystems>
</HOO>
<HOO>
<HOOId>c05ca620-e6f7-4f3d-ab41-3db5fc4faf84</HOOId>
<HOOName>Celine Dior</HOOName>
<IsImpacted>true</IsImpacted>
<Comment>Impact in EPOS and possible Genesys.</Comment>
<IsApproved>true</IsApproved>
<ITSystems>
<Id>2</Id>
<HOOId>c05ca620-e6f7-4f3d-ab41-3db5fc4faf84</HOOId>
<ITSystemId>ed74425b-70a8-441d-931b-638b5567c995</ITSystemId>
<ITSystemName>ePOS</ITSystemName>
<Resources>
<Id>2</Id>
<ResourceId>dcb55555-8fdc-4567-b6dc-f6681c02e9bb</ResourceId>
<ResourceName>Vicky Baston</ResourceName>
<StartDate>2011-07-25T00:00:00+03:00</StartDate>
<EndDate>2011-07-29T00:00:00+03:00</EndDate>
</Resources>
</ITSystems>
<ITSystems>
<Id>3</Id>
<HOOId>c05ca620-e6f7-4f3d-ab41-3db5fc4faf84</HOOId>
<ITSystemId>749d4120-6584-4520-b482-393e6ad25eac</ITSystemId>
<ITSystemName>Genesys</ITSystemName>
<Resources>
<Id>3</Id>
<ResourceId>baccce29-dd82-4d57-858d-4e3828078a46</ResourceId>
<ResourceName>Alice Cooker</ResourceName>
<StartDate>2011-07-27T00:00:00+03:00</StartDate>
<EndDate>2011-08-01T00:00:00+03:00</EndDate>
</Resources>
</ITSystems>
</HOO>
<HOO>
<HOOId>88e7e8ba-6d78-4ead-b8ed-b822be540bb6</HOOId>
<HOOName>Stephan Longbow</HOOName>
<IsImpacted>true</IsImpacted>
<Comment>Impact in DWH.</Comment>
<IsApproved>true</IsApproved>
</HOO>
<HOO>
<HOOId>ebf07f56-4fd3-4999-8c32-abbb33ce927e</HOOId>
<HOOName>Andrew Bowling</HOOName>
<IsImpacted>false</IsImpacted>
<Comment>No impact for Mediation</Comment>
<IsApproved>true</IsApproved>
</HOO>
<HOO>
<HOOId>65828ab6-5a89-44b8-b408-7776b468f4ea</HOOId>
<HOOName>Stefy Sarandon</HOOName>
<IsImpacted>false</IsImpacted>
<Comment>No impact.</Comment>
<IsApproved>true</IsApproved>
</HOO>
<HOO>
<HOOId>a993245a-e110-4c79-ae93-079d47f743c5</HOOId>
<HOOName>Dimitri Stopolev</HOOName>
<IsImpacted>true</IsImpacted>
<Comment>Yes. Solution document to be provided.</Comment>
<IsApproved>true</IsApproved>
<ITSystems>
<Id>5</Id>
<HOOId>a993245a-e110-4c79-ae93-079d47f743c5</HOOId>
<ITSystemId>ddf6fd9f-dca0-4f00-8932-738c0b4aff2f</ITSystemId>
<ITSystemName>EA Pack</ITSystemName>
<Comment>Solution document will be provided. The report will be generated based on multiple data sources pending the implementation of contact management functionality in Siebel CRM.</Comment>
<Resources>
<Id>5</Id>
<ResourceId>a993245a-e110-4c79-ae93-079d47f743c5</ResourceId>
<ResourceName>Dimitri Stopolev</ResourceName>
<StartDate>2011-08-10T00:00:00+03:00</StartDate>
<EndDate>2011-08-15T00:00:00+03:00</EndDate>
</Resources>
</ITSystems>
</HOO>
<HOO>
<HOOId>f1af8154-fd12-4e79-b69e-1a1b0dc455d5</HOOId>
<HOOName>Jacob Mappet</HOOName>
<IsImpacted>true</IsImpacted>
<Comment>need to privide reports related to the selfcare channels (IVR/SMS/USSD)</Comment>
<IsApproved>true</IsApproved>
<ITSystems>
<Id>6</Id>
<HOOId>f1af8154-fd12-4e79-b69e-1a1b0dc455d5</HOOId>
<ITSystemId>b87ef2ce-363d-4edc-bdd9-232725e7ecf5</ITSystemId>
<ITSystemName>RT Bus</ITSystemName>
<Resources>
<Id>6</Id>
<ResourceId>f1af8154-fd12-4e79-b69e-1a1b0dc455d5</ResourceId>
<ResourceName>Jacob Mappet</ResourceName>
<StartDate>2011-07-27T00:00:00+03:00</StartDate>
<EndDate>2011-08-12T00:00:00+03:00</EndDate>
</Resources>
</ITSystems>
</HOO>
<HOO>
<HOOId>f349e4a8-c52a-484e-85f0-da3eb693f617</HOOId>
<HOOName>Jean Darius</HOOName>
<IsImpacted>true</IsImpacted>
<Comment>Possible impact in RightNow platform</Comment>
<IsApproved>true</IsApproved>
<ITSystems>
<Id>7</Id>
<HOOId>f349e4a8-c52a-484e-85f0-da3eb693f617</HOOId>
<ITSystemId>6b6722da-c591-4065-a87b-107830e2248a</ITSystemId>
<ITSystemName>Website</ITSystemName>
<Resources>
<Id>7</Id>
<ResourceId>f349e4a8-c52a-484e-85f0-da3eb693f617</ResourceId>
<ResourceName>Jean Darius</ResourceName>
<StartDate>2011-08-07T00:00:00+03:00</StartDate>
<EndDate>2011-08-12T00:00:00+03:00</EndDate>
</Resources>
</ITSystems>
</HOO>
</ITSystemsResourcesDS>

and here's the query:

Select *
From
(
SELECT a.ProjectName,
(
convert(nvarchar(max),isnull(a.[PMS IT Systems And Resources],' '))
+ convert(nvarchar(max),isnull(a.[PMS IT Systems And Resources 2],' '))
+ convert(nvarchar(max),isnull(a.[PMS IT Systems And Resources 3],' '))
+ convert(nvarchar(max),isnull(a.[PMS IT Systems And Resources 4],' '))
+ convert(nvarchar(max),isnull(a.[PMS IT Systems And Resources 5],' '))
) as pms_impact
FROM [ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView] a
where a.ProjectActualStartDate between '26 may 2011' and '19 jan 2012'
and a.[PMS Project Status] in
(
'18 Closed',
'17 Closing',
'14 Ready for Deployment',
'15 Ready for Sanity',
'16 Completed'
)
and (a.[PMS IT Systems And Resources] is not null or a.[PMS IT Systems And Resources] <> '')
and (a.[PMS IT Systems And Resources 2] is not null or a.[PMS IT Systems And Resources 2] <> '')
and (a.[PMS IT Systems And Resources 3] is not null or a.[PMS IT Systems And Resources 3] <> '')
and (a.[PMS IT Systems And Resources 4] is not null or a.[PMS IT Systems And Resources 4] <> '')
and (a.[PMS IT Systems And Resources 5] is not null or a.[PMS IT Systems And Resources 5] <> '')
) b
where CAST(b.pms_impact AS XML).exist('/HOO/HOOName[fn:contains(.,"Jean Darius")]') = 1
AND CAST(b.pms_impact AS XML).exist('/ITSystemsResourcesDS/HOO/IsImpacted[fn:contains(.,"true")]') = 1

Altought I can see that Jean Darius isimpacted=1 the query doesn't return anything.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-22 : 06:31:57

That's because you are using a namespace.

N 56°04'39.26"
E 12°55'05.63"

barb0sul
Starting Member

14 Posts

Posted - 2012-03-22 : 06:34:44

quote:
Originally posted by SwePeso

That's because you are using a namespace.

Can you be more specific please ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-22 : 06:43:35

[code]DECLARE @Sample TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
Data VARCHAR(MAX) NOT NULL
)

INSERT @Sample
VALUES ('<?xml version="1.0" encoding="UTF-8"?><ITSystemsResourcesDS xmlns="http://tempuri.org/ITSystemsResourcesDS.xsd"><HOO><HOOId>4917ef06-6e19-477c-bd35-97cf58fdfad2</HOOId><HOOName>George Popovici</HOOName>
<IsImpacted>true</IsImpacted><Comment>Details about ticklers and other info if needed</Comment><IsApproved>true</IsApproved><ITSystems><Id>1</Id><HOOId>4917ef06-6e19-477c-bd35-97cf58fdfad2</HOOId><ITSystemId>d6670f6b-48c8-4117-b5cf-3aaad2da05f5</ITSystemId>
<ITSystemName>BSCS</ITSystemName><Resources><Id>1</Id><ResourceId>2f13ce71-9088-434b-8fbb-3b94142116ec</ResourceId><ResourceName>Mikael Harnshaw</ResourceName><StartDate>2011-07-18T00:00:00+03:00</StartDate><EndDate>2011-07-22T00:00:00+03:00</EndDate>
</Resources></ITSystems></HOO><HOO><HOOId>c05ca620-e6f7-4f3d-ab41-3db5fc4faf84</HOOId><HOOName>Celine Dior</HOOName><IsImpacted>true</IsImpacted><Comment>Impact in EPOS and possible Genesys.</Comment>
<IsApproved>true</IsApproved><ITSystems><Id>2</Id><HOOId>c05ca620-e6f7-4f3d-ab41-3db5fc4faf84</HOOId><ITSystemId>ed74425b-70a8-441d-931b-638b5567c995</ITSystemId>
<ITSystemName>ePOS</ITSystemName><Resources><Id>2</Id><ResourceId>dcb55555-8fdc-4567-b6dc-f6681c02e9bb</ResourceId><ResourceName>Vicky Baston</ResourceName><StartDate>2011-07-25T00:00:00+03:00</StartDate>
<EndDate>2011-07-29T00:00:00+03:00</EndDate></Resources></ITSystems><ITSystems><Id>3</Id><HOOId>c05ca620-e6f7-4f3d-ab41-3db5fc4faf84</HOOId><ITSystemId>749d4120-6584-4520-b482-393e6ad25eac</ITSystemId>
<ITSystemName>Genesys</ITSystemName><Resources><Id>3</Id><ResourceId>baccce29-dd82-4d57-858d-4e3828078a46</ResourceId><ResourceName>Alice Cooker</ResourceName>
<StartDate>2011-07-27T00:00:00+03:00</StartDate><EndDate>2011-08-01T00:00:00+03:00</EndDate></Resources></ITSystems></HOO><HOO><HOOId>88e7e8ba-6d78-4ead-b8ed-b822be540bb6</HOOId><HOOName>Stephan Longbow</HOOName>
<IsImpacted>true</IsImpacted><Comment>Impact in DWH.</Comment><IsApproved>true</IsApproved></HOO><HOO><HOOId>ebf07f56-4fd3-4999-8c32-abbb33ce927e</HOOId><HOOName>Andrew Bowling</HOOName>
<IsImpacted>false</IsImpacted><Comment>No impact for Mediation</Comment><IsApproved>true</IsApproved></HOO><HOO><HOOId>65828ab6-5a89-44b8-b408-7776b468f4ea</HOOId>
<HOOName>Stefy Sarandon</HOOName><IsImpacted>false</IsImpacted><Comment>No impact.</Comment><IsApproved>true</IsApproved></HOO>
<HOO><HOOId>a993245a-e110-4c79-ae93-079d47f743c5</HOOId><HOOName>Dimitri Stopolev</HOOName><IsImpacted>true</IsImpacted><Comment>Yes. Solution document to be provided.</Comment><IsApproved>true</IsApproved>
<ITSystems><Id>5</Id><HOOId>a993245a-e110-4c79-ae93-079d47f743c5</HOOId><ITSystemId>ddf6fd9f-dca0-4f00-8932-738c0b4aff2f</ITSystemId><ITSystemName>EA Pack</ITSystemName>
<Comment>Solution document will be provided. The report will be generated based on multiple data sources pending the implementation of contact management functionality in Siebel CRM.</Comment>
<Resources><Id>5</Id><ResourceId>a993245a-e110-4c79-ae93-079d47f743c5</ResourceId><ResourceName>Dimitri Stopolev</ResourceName><StartDate>2011-08-10T00:00:00+03:00</StartDate><EndDate>2011-08-15T00:00:00+03:00</EndDate>
</Resources></ITSystems></HOO><HOO><HOOId>f1af8154-fd12-4e79-b69e-1a1b0dc455d5</HOOId><HOOName>Jacob Mappet</HOOName><IsImpacted>true</IsImpacted><Comment>need to privide reports related to the selfcare channels (IVR/SMS/USSD)</Comment>
<IsApproved>true</IsApproved><ITSystems><Id>6</Id><HOOId>f1af8154-fd12-4e79-b69e-1a1b0dc455d5</HOOId><ITSystemId>b87ef2ce-363d-4edc-bdd9-232725e7ecf5</ITSystemId><ITSystemName>RT Bus</ITSystemName><Resources>
<Id>6</Id><ResourceId>f1af8154-fd12-4e79-b69e-1a1b0dc455d5</ResourceId><ResourceName>Jacob Mappet</ResourceName><StartDate>2011-07-27T00:00:00+03:00</StartDate><EndDate>2011-08-12T00:00:00+03:00</EndDate></Resources>
</ITSystems></HOO><HOO><HOOId>f349e4a8-c52a-484e-85f0-da3eb693f617</HOOId><HOOName>Jean Darius</HOOName><IsImpacted>true</IsImpacted><Comment>Possible impact in RightNow platform</Comment><IsApproved>true</IsApproved>
<ITSystems><Id>7</Id><HOOId>f349e4a8-c52a-484e-85f0-da3eb693f617</HOOId><ITSystemId>6b6722da-c591-4065-a87b-107830e2248a</ITSystemId><ITSystemName>Website</ITSystemName><Resources><Id>7</Id><ResourceId>f349e4a8-c52a-484e-85f0-da3eb693f617</ResourceId>
<ResourceName>Jean Darius</ResourceName><StartDate>2011-08-07T00:00:00+03:00</StartDate><EndDate>2011-08-12T00:00:00+03:00</EndDate></Resources></ITSystems></HOO></ITSystemsResourcesDS>')

-- Solution by SwePeso
;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/ITSystemsResourcesDS.xsd')
SELECT ID
FROM @Sample
WHERE CAST(Data AS XML).exist('(/ITSystemsResourcesDS/HOO/HOOName[. = "Jean Darius"])') = 1
AND CAST(Data AS XML).exist('(/ITSystemsResourcesDS/HOO/IsImpacted[. = "true"])') = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-22 : 06:48:59

[code];WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/ITSystemsResourcesDS.xsd')
SELECT ProjectName,
pms_impact
FROM (
SELECT ProjectName,
(
CAST([PMS IT Systems And Resources] AS VARCHAR(MAX))
+ CAST([PMS IT Systems And Resources 2] AS VARCHAR(MAX))
+ CAST([PMS IT Systems And Resources 3] AS VARCHAR(MAX))
+ CAST([PMS IT Systems And Resources 4] AS VARCHAR(MAX))
+ CAST([PMS IT Systems And Resources 5] AS VARCHAR(MAX))
) AS pms_impact
FROM ProjectServer_Reporting.dbo.MSP_EpmProject_UserView
WHERE ProjectActualStartDate BETWEEN '26 may 2011' AND '19 jan 2012'
AND [PMS Project Status] IN ('18 Closed', '17 Closing', '14 Ready for Deployment', '15 Ready for Sanity', '16 Completed')
and [PMS IT Systems And Resources] > ''
and [PMS IT Systems And Resources 2] > ''
and [PMS IT Systems And Resources 3] > ''
and [PMS IT Systems And Resources 4] > ''
and [PMS IT Systems And Resources 5] > ''
) AS d
WHERE CAST(pms_Impact AS XML).exist('(/ITSystemsResourcesDS/HOO/HOOName[. = "Jean Darius"])') = 1
AND CAST(pms_Impact AS XML).exist('(/ITSystemsResourcesDS/HOO/IsImpacted[. = "true"])') = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

barb0sul
Starting Member

14 Posts

Posted - 2012-03-22 : 07:56:42

Msg 207, Level 16, State 1, Line 30
Invalid column name 'Data'.

:(

I'm so frustrated ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-22 : 08:02:42

Change "Data" to "pms_Impact".
See my edited previous answer.

N 56°04'39.26"
E 12°55'05.63"

Next Page