Running XQuery on a text field that's been CAST to XML

Question

I'm trying to use XQuery on text data that I've cast to XML, without success. So this fails:

    DECLARE @myDoc TEXT  
    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>'  

SELECT CAST(@myDoc AS XML)('(/Root/ProductDescription/@ProductID)[1]', 'int' )

but this succeeds:

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>'  

SELECT  @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )

use `NVARCHAR(MAX)` not `TEXT`. And `CAST(@myDoc AS XML).value( . . ` — David Browne - Microsoft, Jan 23 '20 at 17:25
`SELECT CAST(@myDoc AS XML).value('(/Root/ProductDescription/@ProductID)[1]', 'int' )` returns Msg 227, Level 15, State 1, Line 11 "VALUE" is not a valid function, property, or field. — Eric, Jan 23 '20 at 20:18

score 0 · Accepted Answer · answered Jan 27 '20 at 19:31

I figured out what David Brown was talking about, this works:

DECLARE @myDoc NVARCHAR(MAX)  
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>'  

SELECT CAST(@myDoc AS XML).value('(/Root/ProductDescription/@ProductID)[1]', 'int' )

Running XQuery on a text field that's been CAST to XML

1 Answers1