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
 Other SQL Server Topics (2005)
 XML Column in SQL Server 2005

Author  Topic 

kartikkumar84
Starting Member

7 Posts
Posted - 2009-09-28 : 05:11:45
Hi, I have a XML column (name = 'xml') in a table 'contact' in my DB which has rows as the following

<Contact xmlns="http://www.focus-solutions.co.uk/focus360/1.0" id="00496bb7-0046-4459-8cf8-002cc5921f4c" documentId="494f4671-efe8-4577-aecc-4424b699a89c">
<Type code="FirstMeeting">First meeting</Type>
<Location code="ClientHome">Client Home</Location>
<PresentationInd>true</PresentationInd>
<OtherAddress>
<HouseNameNumber>1</HouseNameNumber>
<AddressLine1>Royal Terrace Gardens</AddressLine1>
<City>Edinburgh</City>
<Postcode>EH7 5DX</Postcode>
</OtherAddress>
<Date>2009-09-08</Date>
<Time isoTime="15:00:00">
<Hours>03</Hours>
<Minutes>00</Minutes>
<Period>PM</Period>
</Time>
<Duration>
<Hours>01</Hours>
<Minutes>00</Minutes>
</Duration>
<Notes />
<LetterSentInd>true</LetterSentInd>
<Documents>
<Document id="a7b36eec-ed51-4f22-8465-267a893fd8a0" externalId="494f4671-efe8-4577-aecc-4424b699a89c" userAttached="false">
<Type code="ConfirmationLetter">Confirmation Letter</Type>
<Reference>Joe Marton First meeting</Reference>
<CreatedDate>2009-09-18</CreatedDate>
</Document>
</Documents>
<CustomQuestions>
<Question type="HasMeetingTakenPlace">
<Answer>true</Answer>
</Question>
<Question type="ClientContacted">
<Answer>Joe Marton</Answer>
</Question>
</CustomQuestions>
<Valid>true</Valid>
</Contact>

So this is just one row in the table.I am complete beginner when it comes to querying XML datatypes in a table.

I need to be able to list all records in the table CONTACT for which the the value <PresentationInd>true</PresentationInd> is true.

Help appriciated!
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-09-28 : 05:36:52
See http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

kartikkumar84
Starting Member

7 Posts
Posted - 2009-09-28 : 05:54:56
Thanks Peso, but it doesnt seem to be the same topic. I have little/no knowledge about XML datatypes or files. Can you please direct me to somewhere where it is explained thoroughly or write the SQL for the above question so I can use it in 19 other tables where I need to get specific data from XML columns. All my XML columns have similar structure. I want the query fairly quickly to use in my testing.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-09-28 : 06:14:36
And you can't get then from the link I posted?
DECLARE	@Sample TABLE
	(
		RowID INT IDENTITY(1, 1) PRIMARY KEY,
		Data XML
	)

INSERT	@Sample	
SELECT	'	<Contact xmlns="http://www.focus-solutions.co.uk/focus360/1.0" id="00496bb7-0046-4459-8cf8-002cc5921f4c" documentId="494f4671-efe8-4577-aecc-4424b699a89c">
			<Type code="FirstMeeting">First meeting</Type>
			<Location code="ClientHome">Client Home</Location>
			<PresentationInd>true</PresentationInd>
			<OtherAddress>
				<HouseNameNumber>1</HouseNameNumber>
				<AddressLine1>Royal Terrace Gardens</AddressLine1>
				<City>Edinburgh</City>
				<Postcode>EH7 5DX</Postcode>
			</OtherAddress>
			<Date>2009-09-08</Date>
			<Time isoTime="15:00:00">
				<Hours>03</Hours>
				<Minutes>00</Minutes>
				<Period>PM</Period>
			</Time>
			<Duration>
				<Hours>01</Hours>
				<Minutes>00</Minutes>
			</Duration>
			<Notes />
			<LetterSentInd>true</LetterSentInd>
			<Documents>
				<Document id="a7b36eec-ed51-4f22-8465-267a893fd8a0" externalId="494f4671-efe8-4577-aecc-4424b699a89c" userAttached="false">
					<Type code="ConfirmationLetter">Confirmation Letter</Type>
					<Reference>Joe Marton First meeting</Reference>
					<CreatedDate>2009-09-18</CreatedDate>
				</Document>
			</Documents>
			<CustomQuestions>
				<Question type="HasMeetingTakenPlace">
					<Answer>true</Answer>
				</Question>
				<Question type="ClientContacted">
					<Answer>Joe Marton</Answer>
				</Question>
			</CustomQuestions>
			<Valid>true</Valid>
		</Contact>'

;WITH XMLNAMESPACES('http://www.focus-solutions.co.uk/focus360/1.0' AS peso)
SELECT	RowID,
	Data
FROM	@Sample
WHERE	Data.exist('/peso:Contact/peso:PresentationInd/text()[. = "true"]') = 1


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

kartikkumar84
Starting Member

7 Posts
Posted - 2009-09-28 : 06:31:42
Thanks a LOT!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-09-28 : 06:33:44
You're welcome.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

kartikkumar84
Starting Member

7 Posts
Posted - 2009-09-29 : 09:08:22
Hi have a similar question based on the above XML column in the table. Basically, I had modified the above query for my table and db.


WITH XMLNAMESPACES('http://www.focus-solutions.co.uk/focus360/1.0' as xmlquery)
select *
from focus360.contact
where xml.exist('/xmlquery:Contact/xmlquery:PresentationInd/text()[. = "true"]') = 1


The above query will only display the rows for which the value of the 'PresentationInd' is TRUE. How can I display the value of the above field 'PresentationInd' itself from the contacts table so
I can get the following result set for example:

true
true
false
true
false
false
....

So in short how can displaye the value of specific fields within the XML column in the contacts table.

Thanks in advance
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-09-30 : 03:21:43
Here are some example from the link I posted above
DECLARE	@Sample TABLE
	(
		RowID INT IDENTITY(1, 1) PRIMARY KEY,
		Data XML
	)

INSERT	@Sample	
SELECT	'	<Contact xmlns="http://www.focus-solutions.co.uk/focus360/1.0" id="00496bb7-0046-4459-8cf8-002cc5921f4c" documentId="494f4671-efe8-4577-aecc-4424b699a89c">
			<Type code="FirstMeeting">First meeting</Type>
			<Location code="ClientHome">Client Home</Location>
			<PresentationInd>true</PresentationInd>
			<OtherAddress>
				<HouseNameNumber>1</HouseNameNumber>
				<AddressLine1>Royal Terrace Gardens</AddressLine1>
				<City>Edinburgh</City>
				<Postcode>EH7 5DX</Postcode>
			</OtherAddress>
			<Date>2009-09-08</Date>
			<Time isoTime="15:00:00">
				<Hours>03</Hours>
				<Minutes>00</Minutes>
				<Period>PM</Period>
			</Time>
			<Duration>
				<Hours>01</Hours>
				<Minutes>00</Minutes>
			</Duration>
			<Notes />
			<LetterSentInd>true</LetterSentInd>
			<Documents>
				<Document id="a7b36eec-ed51-4f22-8465-267a893fd8a0" externalId="494f4671-efe8-4577-aecc-4424b699a89c" userAttached="false">
					<Type code="ConfirmationLetter">Confirmation Letter</Type>
					<Reference>Joe Marton First meeting</Reference>
					<CreatedDate>2009-09-18</CreatedDate>
				</Document>
			</Documents>
			<CustomQuestions>
				<Question type="HasMeetingTakenPlace">
					<Answer>true</Answer>
				</Question>
				<Question type="ClientContacted">
					<Answer>Joe Marton</Answer>
				</Question>
			</CustomQuestions>
			<Valid>true</Valid>
		</Contact>'

;WITH XMLNAMESPACES('http://www.focus-solutions.co.uk/focus360/1.0' AS peso)
SELECT	RowID,
	Data.value('/peso:Contact[1]/@documentId', 'UNIQUEIDENTIFIER') AS documentId,
	Data.value('/peso:Contact[1]/peso:PresentationInd[1]/text()[1]', 'BIT') AS PresentationInd1,
	Data.value('/peso:Contact[1]/peso:PresentationInd[1]/text()[1]', 'VARCHAR(MAX)') AS PresentationInd2
FROM	@Sample


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site