Tutorial :Using XQuery in SQL Server 2005 to get XML Inner Text



Question:

How do I select the inner text of an XML node using XQuery?

Microsoft Books Online shows how to retrive an attribute below:

DECLARE @myDoc xml  DECLARE @ProdID int  SET @myDoc = '<Root>  <ProductDescription ProductID="1" ProductName="Road Bike">  <Features>    <Warranty>1 year parts and labor</Warranty>    <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>  </Features>  </ProductDescription>  </Root>'    SET @ProdID =  @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )  SELECT @ProdID  

How would I get the inner text value of the Warranty node?


Solution:1

Something like this:

DECLARE @Warranty VARCHAR(50)    SET @Warranty = @myDoc.value('(/Root/ProductDescription/Features/Warranty/text())[1]', 'varchar(50)' )    SELECT @Warranty  

Marc


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »