How to get child element and attribute value with condition from xml data in sql server?

Question

How to get child value and its attribute value with condition in sql server.? How to get this

<CheckoutAttribute ID="9">
<CheckoutAttributeValue><Value>26</Value></CheckoutAttributeValue>

CheckoutAttributeID CheckoutAttributeValue
        9                    26

from following xml data.

<Attributes>
<CheckoutAttribute ID="4">
<CheckoutAttributeValue><Value>18</Value></CheckoutAttributeValue>
</CheckoutAttribute>
<CheckoutAttribute ID="6">
<CheckoutAttributeValue><Value>22</Value></CheckoutAttributeValue>
<CheckoutAttributeValue><Value>23</Value></CheckoutAttributeValue>
</CheckoutAttribute>
<CheckoutAttribute ID="9">
<CheckoutAttributeValue><Value>26</Value></CheckoutAttributeValue>
<CheckoutAttributeValue><Value>27</Value></CheckoutAttributeValue>
</CheckoutAttribute>
<CheckoutAttribute ID="1">
<CheckoutAttributeValue><Value>1</Value></CheckoutAttributeValue>
</CheckoutAttribute>
</Attributes>

You sometimes have 2 checkout attribute values for each checkout attribute - do you want them on one row or 2? Please show the desired output for the whole of your sample input. — BeanFrog
– BeanFrog, Commented Feb 23, 2017 at 14:57
I want only one row for checkoutattribute id 9 with value 26. — Musakkhir Sayyed
– Musakkhir Sayyed, Commented Feb 23, 2017 at 15:01

Musakkhir Sayyed · Accepted Answer · 2017-02-23 15:06:43Z

1

CREATE TABLE #tempTable ( id INT IDENTITY(1,1), xmlDataTest xml)
INSERT INTO #TempTable ( xmlDataTest)
VALUES ('<Attributes>
<CheckoutAttribute ID="4">
<CheckoutAttributeValue><Value>18</Value></CheckoutAttributeValue>
</CheckoutAttribute>
<CheckoutAttribute ID="6">
<CheckoutAttributeValue><Value>22</Value></CheckoutAttributeValue>
<CheckoutAttributeValue><Value>23</Value></CheckoutAttributeValue>
</CheckoutAttribute>
<CheckoutAttribute ID="9">
<CheckoutAttributeValue><Value>26</Value></CheckoutAttributeValue>
<CheckoutAttributeValue><Value>27</Value></CheckoutAttributeValue>
</CheckoutAttribute>
<CheckoutAttribute ID="1">
<CheckoutAttributeValue><Value>1</Value></CheckoutAttributeValue>
</CheckoutAttribute>
</Attributes>')

SELECT r.value('@ID','int') AS CheckoutAttributeID
        , r.query('data(CheckoutAttributeValue/Value)[1]') AS CheckoutAttributeValue
FROM #tempTable
CROSS APPLY xmlDataTest.nodes('/Attributes/CheckoutAttribute') AS x(r)
WHERE
r.value('@ID','int') = 9 

DROP TABLE #tempTable

edited Feb 23, 2017 at 15:06

Musakkhir Sayyed

7,21813 gold badges44 silver badges67 bronze badges

answered Feb 23, 2017 at 14:14

manderson

8891 gold badge7 silver badges21 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

Hi manderson, in such cases its better to use .nodes('/Attributes/CheckoutAttribute[@ID="9"]') (or introduce this value with sql:column() or sql:variable()). Your solution has to read the whole lot, just to throw most of it away...

Collectives™ on Stack Overflow

How to get child element and attribute value with condition from xml data in sql server?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related