Select multiple values from XML in SQL Server

Question

I have a @MyXMLString parameter being passed to a stored procedure in the following format:

DECLARE @MyXMLString XML
SET @MyXMLString =
'<data formID="2">
 <UnAssigned AcctTypeID="1"/><UnAssigned AcctTypeID="2"/>
 </data>'

In the stored procedure, I have to retrieve the value of formID and AcctTypeID and then update a table.

Now there will be only one formID, which I am able to retrieve

DECLARE @formID int
SET @formID = @MyXMLString.value('(data/@formID)[1]','int')

But there can be one or multiple AcctTypeID, i.e. one or more of '<UnAssigned AcctTypeID="1"/>'. I need help in figuring out how to retrieve it.

marc_s · Accepted Answer · 2016-09-09 20:26:07Z

3

You can fetch all instances of AcctTypeID like this:

SELECT 
    XC.value('@AcctTypeID', 'int')
FROM
    @MyXMLString.nodes('/data/UnAssigned') AS XT(XC)

When you execute this, these are the results:

edited Sep 9, 2016 at 20:26

answered Sep 9, 2016 at 16:05

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SilverFish Over a year ago

It is returning two rows of NULL and not getting actual values of AcctTypeID.

marc_s Over a year ago

@SilverFish: then you haven't showed us the actual XML that you're getting - with the XML you've posted, this code works .... Also: be careful, XML processing is case sensitive! Make sure to have all element and/or attribute names cased correctly !

Paweł Dyl · Accepted Answer · 2016-09-09 17:26:36Z

0

This should work:

DECLARE @MyXMLString XML
SET @MyXMLString =
'<data formID="2">
 <UnAssigned AcctTypeID="1"/><UnAssigned AcctTypeID="2"/>
 </data>'

SELECT X.value('../@formID', 'int') FormID, X.value('@AcctTypeID', 'int') AcctTypeID
FROM @MyXMLString.nodes('data/UnAssigned') T(X)

Result

FormID      AcctTypeID
----------- -----------
2           1
2           2

answered Sep 9, 2016 at 17:26

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

Collectives™ on Stack Overflow

Select multiple values from XML in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related