How to fetch all data from XML column in SQL Server

Question

I am trying to fetch the data from a XML column. This is the query and below it is the xml column data

SELECT 
    [PQDAdvice].value('(/Advice//DORowData/PrimaryValue/node())[1]', 'nvarchar(max)') as PrimaryValue,
    [PQDAdvice].value('(/Advice//DORowData/ListValue1/node())[1]', 'nvarchar(max)') as ListValue1
FROM 
    PatQD 
WHERE
    PQDPatID = '4c983bd8-da00-4395-80bb-a383b21313d5'

XML contents:

<Advice>
    <DORowData>
        <PrimaryValue>Diet and Nutrition</PrimaryValue>
        <ListValue1>Advice</ListValue1>
    </DORowData>
    <DORowData>
        <PrimaryValue>Salt Restriction</PrimaryValue>
        <ListValue1>Advice</ListValue1>
    </DORowData>
    <DORowData>
        <PrimaryValue>Water Consumption</PrimaryValue>
        <ListValue1>Advice</ListValue1>
    </DORowData>
</Advice>

The issue I am getting that only one column I am getting instead of all column

Primary            |ListValue1
Diet and Nutrition |Advice

Instead of

Primary            |ListValue1
Diet and Nutrition |Advice
Salt Restriction   |Advice
Water Consumption  |Advice

Devart · Accepted Answer · 2016-01-22 10:29:17Z

1

DECLARE @xml XML = '
<Advice>
    <DORowData>
        <PrimaryValue>Diet and Nutrition</PrimaryValue>
        <ListValue1>Advice</ListValue1>
    </DORowData>
    <DORowData>
        <PrimaryValue>Salt Restriction</PrimaryValue>
        <ListValue1>Advice</ListValue1>
    </DORowData>
    <DORowData>
        <PrimaryValue>Water Consumption</PrimaryValue>
        <ListValue1>Advice</ListValue1>
    </DORowData>
</Advice>'

SELECT 
    t.c.value('PrimaryValue[1]', 'nvarchar(max)') as PrimaryValue,
    t.c.value('ListValue1[1]', 'nvarchar(max)') as ListValue1
FROM @xml.nodes('Advice/DORowData') t(c)

Output -

PrimaryValue            ListValue1
----------------------- ------------
Diet and Nutrition      Advice
Salt Restriction        Advice
Water Consumption       Advice

Your query -

SELECT
    PrimaryValue = t.c.value('PrimaryValue[1]', 'NVARCHAR(1000)'),
    ListValue1 = t.c.value('ListValue1[1]', 'NVARCHAR(1000)')
FROM PatQD
CROSS APPLY PQDAdvice.nodes('Advice/DORowData') t(c)
where PQDPatID = '4c983bd8-da00-4395-80bb-a383b21313d5'

edited Jan 22, 2016 at 10:29

answered Jan 22, 2016 at 10:25

Devart

120k23 gold badges172 silver badges189 bronze badges

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

3 Comments

user5825657 Over a year ago

not getting it as the column name where xml is present is [PQDAdvice] and table name is PatQD then how to apply this

Devart Over a year ago

Please check the update.. And few words about performance - blog.devart.com/xml-xquery-perfomance-issues.html

Gottfried Lesigang Over a year ago

@Devart 'cause so many users forget / don't know about voting and accepting SO profs have to help each other :-) Happy coding!

Gottfried Lesigang · Accepted Answer · 2016-01-22 10:32:04Z

1

Try it like this

DECLARE @x XML=
'<Advice>
    <DORowData>
        <PrimaryValue>Diet and Nutrition</PrimaryValue>
        <ListValue1>Advice</ListValue1>
    </DORowData>
    <DORowData>
        <PrimaryValue>Salt Restriction</PrimaryValue>
        <ListValue1>Advice</ListValue1>
    </DORowData>
    <DORowData>
        <PrimaryValue>Water Consumption</PrimaryValue>
        <ListValue1>Advice</ListValue1>
    </DORowData>
</Advice>';

SELECT DoRowData.value('PrimaryValue[1]','varchar(max)') AS PrimaryValue
      ,DoRowData.value('ListValue1[1]','varchar(max)') AS ListValue1
FROM @x.nodes('/Advice/DORowData') AS One(DORowData)

answered Jan 22, 2016 at 10:32

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

How to fetch all data from XML column in SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related