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? – Remus Rusanu Jun 27 '12 at 17:09
  • Yeah probably, I'm not very experience with parsing XML using MSSQL – J Max Jun 27 '12 at 17:14
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, privacy policy and cookie policy

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