XML Column in SQL Server 2005
-
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
-
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
Here is the sample for XML Data Processing.. -- Create the table. CREATE TABLE T ( ProductID int primary key, CatalogDescription xml) GO -- Insert sample data. INSERT INTO T values(1, ';') GO SELECT ProductID, CatalogDescription.value('(/ProductDescription/@ProductName)[1]', 'varchar(40)') AS PName FROM T GO So for your requirement SELECT COLUMNNAME.value('(/Contact/PresentationInd)[1]', 'varchar(5)') AS PresentationInd
modified on Monday, September 28, 2009 6:57 AM