Building a string from an xml

soozh
soozh used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Where is this XML? Is it in the database object, in the file, etc?
soozhCEO

Author

Commented:
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?
soozhCEO

Author

Commented:
yes this code is in a stored procedure
Business Intelligence Developer and Analyst
Top Expert 2015
Commented:
Try below code.


declare @xmlData as 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>'


SELECT CASE WHEN CAST(@xmlData.query('/root/Filters/Filter/Name/text()') AS NVARCHAR(30))='diagnose' THEN 
REPLACE(REPLACE(REPLACE(CAST(@xmlData.query('/root/Filters/Filter/Values/Value') AS NVARCHAR(MAX)),'</Value><Value>',','),'<Value>',''),'</Value>','')
ELSE ''
END


----AS FormGuid

--SELECT REPLACE(REPLACE(REPLACE(CAST(@xmlData.query('/root/Filters/Filter/Values/Value') AS NVARCHAR(MAX)),'</Value><Value>',','),'<Value>',''),'</Value>','')

--SELECT @xmlData.query('/root/Filters/Filter/Name/text()/data/item[@name="form-data-guid"]/text()') AS FormGuid




--DECLARE @x xml
--SET @x = '<ROOT><a>111</a>
--<a>222</a> </ROOT>'
--SELECT @x.query('/ROOT/a')

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial