1

I know the contents of @message_body are:

<inserted DocNo="xxxx99999"/>

I want to put 'DocNo' into a variable I created @DocNo and have tried various this such as:

SET @DocNo = (SELECT ref.value('@DocNo', 'nvarchar(255)')
              FROM @message_body.nodes('//Sample_Message') T(ref))

with no luck. I don't have much experience parsing XML with SQL. Any suggestions?

2
  • There is no XML element Sample_Message. Shouldn't you be querying for @message_body.nodes('//inserted') instead? Jun 27, 2012 at 17:09
  • Yeah probably, I'm not very experience with parsing XML using MSSQL
    – J Max
    Jun 27, 2012 at 17:14

1 Answer 1

4
DECLARE @docNo nvarchar(255)
DECLARE @message_body xml = N'<inserted DocNo="12345"/>'

SELECT
    @docNo = @message_body.value('/inserted[1]/@DocNo', 'nvarchar(255)')

SELECT @docNo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.