CODEWITHSUNDEEP
sql-serverxml
user5825657
user5825657

Reputation:

How to fetch all data from XML column in SQL Server

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

Upvotes: 2

Views: 40

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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)

Upvotes: 1

Devart
Devart

Reputation: 121912

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'

Upvotes: 1

Related Questions