Link to home
Start Free TrialLog in
Avatar of soozh
soozhFlag for Sweden

asked on

Building a string from an xml

Hello!

Can someone help me with some sql that will return to me some data from an xml object?

I have the following xml:
<root>
  <FromDate>2016-03-01</FromDate>
  <ToDate>2016-03-25</ToDate>
  <Variables>
    <Variable>
      <Name>pain</Name>
    </Variable>
    <Variable>
      <Name>unit</Name>
    </Variable>
  </Variables>
  <Filters>
    <Filter>
      <Name>diagnose</Name>
      <Values>
        <Value>1461</Value>
        <Value>1462</Value>
      </Values>
    </Filter>
  </Filters>
</root>

Open in new window

I would like to extract the text of the <Value> nodes under the <root> <Filters> <Filter><Values> where the text in <root> <Filters> <Filter><Name> is "diagnose".

The resuting nvarchar should be "1461, 1462"

I am used to building strings like this from tables using something like:
 select @CategoryAxis = @CategoryAxis + '<Category>' +cast( measurementdate as nvarchar(12) ) + '</Category> ' from MeasurementStatisticsPerUnit

Open in new window

but i just cannot get my head arround XML.
Avatar of YZlat
YZlat
Flag of United States of America image

Where is this XML? Is it in the database object, in the file, etc?
Avatar of soozh

ASKER

the xml is in a variable.

declare @d xml ;

set @d = '<root>
  <FromDate>2016-03-01</FromDate>
  <ToDate>2016-03-25</ToDate>
  <Variables>
    <Variable>
      <Name>pain</Name>
    </Variable>
    <Variable>
      <Name>unit</Name>
    </Variable>
  </Variables>
  <Filters>
    <Filter>
      <Name>diagnose</Name>
      <Values>
        <Value>1461</Value>
        <Value>1462</Value>
      </Values>
    </Filter>
  </Filters>
</root>' ;
Is your xml variable inside of a stored procedure?
Avatar of soozh

ASKER

yes this code is in a stored procedure
ASKER CERTIFIED SOLUTION
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial