select multiple values from xml column

Question

I have the following xml column in SQL:

code

<survey id="32" comments="true" revision="12" complete="true">
  <datasources />
  <questions>
    <question id="0" key="buna_ziua_numele_meu_este" answerRequired="true" visible="true">
      <text>Buna ziua, numele meu este ……...si va sun din partea BCR Asigurari de Viata Vienna Insurance Group SA.  
As putea vorbi cu Dl./Dna [Nume, Prenume]?</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="_jelejov5c2m" target="2" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Da</text>
          <value />
        </answer>
        <answer id="1" key="nu_poate_discuta_doreste_sa" target="1" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Nu poate discuta, doreste sa revin</text>
          <value />
        </answer>
        <answer id="2" key="nu_doreste_stop" target="6" option_group="1" type="text" datasource="" selected="true" required="false" score="0">
          <triggers />
          <text>Nu a raspuns</text>
          <value />
        </answer>
        <answer id="3" key="numar_gresit_alta_persoana" target="6" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Numar Gresit / Alta persoana</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="1" key="in_cazul_acesta_cand_ar" answerRequired="false" visible="true">
      <text>In cazul acesta, cand ar fi un moment potrivit pentru dumneavoastra sa revin cu un apel? 
[se asteapta propunere client; in cazul in care nu concretizeaza o propunere, se ia initiativa] in data de……… la ora …………… este bine?</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="se_noteaza_cand_poate_discuta" target="6" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Se noteaza cand poate discuta/cand doreste sa se revina si se creeaza callback – stop</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="2" key="doamna_domnule_va_contactez_in" answerRequired="true" visible="true">
      <text>Doamna / Domnule va contactez in legatura cu polita de asigurare de tip .......  seria
 [policy number] care a ajuns la maturitate la data de [maturity date].

Pentru inceput doresc sa va informez ca pentru imbunatatirea serviciilor oferite precum si pentru captarea unor informatii noi, acest apel poate fi inregistrat, va rugam sa ne comunicati daca sunteti de acord!</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="este_de_acord_se_codeaza" target="3" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Este de acord - Se codeaza</text>
          <value />
        </answer>
        <answer id="1" key="nu_este_de_acord_se" target="3" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Nu este de acord - se codeaza</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="3" key="pentru_inceput_va_rugam_sa" answerRequired="true" visible="true">
      <text>Pentru inceput, va rugam sa  ne comunicati numele complet si,data nasterii</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="clientul_trebuie_sa_raspunda_corect" target="4" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Clientul trebuie sa raspunda corect la intrebari pentru a putea merge mai departe</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="4" key="contractant_beneficiar_dorim_sa_va" answerRequired="true" visible="true">
      <text>**CONTRACTANT = BENEFICIAR

Dorim sa va reamintim ca in data de [data], contractul dvs de asigurare [nr polita] a ajuns la maturitate si puteti intra în posesia indemnizației de asigurare în valoare de: [SUMA][valuta].
Puteti alege sa reinvestiti valoarea la maturitate sau sa o incasati, pentru orice detalii colegii nostri din unitatile BCR putand sa va ofere toate detaliile necesare.
Daca nu doriti sa mergeti intr-o unitate BCR, va rugam sa confirmam impreuna adresa dvs de email si va vom transmite detaliile necesare incasarii indemnizatiei de asigurare.


*CONTRACTANT ≠ BENEFICIAR

Dorim sa va reamintim ca in data de [data], contractul dvs de asigurare [nr polita] a ajuns la maturitate si beneficiarul/beneficiarii dvs [nume beneficiar/beneficiari] poate/pot incasa indemnizația de asigurare. 
Va rugăm să ii transmiteti acestuia vestea buna, cu rugămintea de a merge în orice sucursală a băncii BCR pentru a face demersurile necesare.

Daca nu doreste sa mearga intr-o unitate BCR, va rugam sa ii transmiteti datele noastre de contact [adresa email si nr call center] pentru a ne contacta si ii vom transmite detaliile necesare incasarii indemnizatiei de asigurare.</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="va_merge_intro_unitate_bcr" target="5" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Va merge intr-o unitate BCR</text>
          <value />
        </answer>
        <answer id="1" key="doreste_incasarea_indemnizatiei_de_asigurare" target="5" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Doreste incasarea indemnizatiei de asigurare</text>
          <value />
        </answer>
        <answer id="2" key="doreste_sa_afle_oferta_bcrl" target="5" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Doreste sa afle oferta BCRL</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="5" key="va_rugam_sa_ne_spuneti" answerRequired="false" visible="true">
      <text>Va rugam sa ne spuneti daca va mai putem furniza si alte detalii....</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="da_oferim_informatia_de_care" target="6" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Da ( oferim informatia de care are nevoie )</text>
          <value />
        </answer>
        <answer id="1" key="_g2adhjvk8t5" target="6" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Nu</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="6" key="multumim_pentru_timpul_acordat_zi" answerRequired="false" visible="true">
      <text>Multumim pentru timpul acordat.
O zi frumoasa!</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="end" target="-1" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>END!</text>
          <value />
        </answer>
      </answers>
    </question>
  </questions>
</survey>

How can I select in SQL the following info: question text and answer text where @selected = true ? I need to extract those info in a report from this xml column. I've tried different solutions found here, without any success.

Please help !

Thanks in advance!

Answer 1

try this (add additional attributes as @Attribute or values as name):

DECLARE @XmlDocument XML = N'<survey id="32" comments="true" revision="12" complete="true">
  <datasources />
  <questions>
    <question id="0" key="buna_ziua_numele_meu_este" answerRequired="true" visible="true">
      <text>Buna ziua, numele meu este ……...si va sun din partea BCR Asigurari de Viata Vienna Insurance Group SA.  
As putea vorbi cu Dl./Dna [Nume, Prenume]?</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="_jelejov5c2m" target="2" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Da</text>
          <value />
        </answer>
        <answer id="1" key="nu_poate_discuta_doreste_sa" target="1" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Nu poate discuta, doreste sa revin</text>
          <value />
        </answer>
        <answer id="2" key="nu_doreste_stop" target="6" option_group="1" type="text" datasource="" selected="true" required="false" score="0">
          <triggers />
          <text>Nu a raspuns</text>
          <value />
        </answer>
        <answer id="3" key="numar_gresit_alta_persoana" target="6" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Numar Gresit / Alta persoana</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="1" key="in_cazul_acesta_cand_ar" answerRequired="false" visible="true">
      <text>In cazul acesta, cand ar fi un moment potrivit pentru dumneavoastra sa revin cu un apel? 
[se asteapta propunere client; in cazul in care nu concretizeaza o propunere, se ia initiativa] in data de……… la ora …………… este bine?</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="se_noteaza_cand_poate_discuta" target="6" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Se noteaza cand poate discuta/cand doreste sa se revina si se creeaza callback – stop</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="2" key="doamna_domnule_va_contactez_in" answerRequired="true" visible="true">
      <text>Doamna / Domnule va contactez in legatura cu polita de asigurare de tip .......  seria
 [policy number] care a ajuns la maturitate la data de [maturity date].

Pentru inceput doresc sa va informez ca pentru imbunatatirea serviciilor oferite precum si pentru captarea unor informatii noi, acest apel poate fi inregistrat, va rugam sa ne comunicati daca sunteti de acord!</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="este_de_acord_se_codeaza" target="3" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Este de acord - Se codeaza</text>
          <value />
        </answer>
        <answer id="1" key="nu_este_de_acord_se" target="3" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Nu este de acord - se codeaza</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="3" key="pentru_inceput_va_rugam_sa" answerRequired="true" visible="true">
      <text>Pentru inceput, va rugam sa  ne comunicati numele complet si,data nasterii</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="clientul_trebuie_sa_raspunda_corect" target="4" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Clientul trebuie sa raspunda corect la intrebari pentru a putea merge mai departe</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="4" key="contractant_beneficiar_dorim_sa_va" answerRequired="true" visible="true">
      <text>**CONTRACTANT = BENEFICIAR

Dorim sa va reamintim ca in data de [data], contractul dvs de asigurare [nr polita] a ajuns la maturitate si puteti intra în posesia indemniza?iei de asigurare în valoare de: [SUMA][valuta].
Puteti alege sa reinvestiti valoarea la maturitate sau sa o incasati, pentru orice detalii colegii nostri din unitatile BCR putand sa va ofere toate detaliile necesare.
Daca nu doriti sa mergeti intr-o unitate BCR, va rugam sa confirmam impreuna adresa dvs de email si va vom transmite detaliile necesare incasarii indemnizatiei de asigurare.


*CONTRACTANT ? BENEFICIAR

Dorim sa va reamintim ca in data de [data], contractul dvs de asigurare [nr polita] a ajuns la maturitate si beneficiarul/beneficiarii dvs [nume beneficiar/beneficiari] poate/pot incasa indemniza?ia de asigurare. 
Va rug?m s? ii transmiteti acestuia vestea buna, cu rug?mintea de a merge în orice sucursal? a b?ncii BCR pentru a face demersurile necesare.

Daca nu doreste sa mearga intr-o unitate BCR, va rugam sa ii transmiteti datele noastre de contact [adresa email si nr call center] pentru a ne contacta si ii vom transmite detaliile necesare incasarii indemnizatiei de asigurare.</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="va_merge_intro_unitate_bcr" target="5" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Va merge intr-o unitate BCR</text>
          <value />
        </answer>
        <answer id="1" key="doreste_incasarea_indemnizatiei_de_asigurare" target="5" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Doreste incasarea indemnizatiei de asigurare</text>
          <value />
        </answer>
        <answer id="2" key="doreste_sa_afle_oferta_bcrl" target="5" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Doreste sa afle oferta BCRL</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="5" key="va_rugam_sa_ne_spuneti" answerRequired="false" visible="true">
      <text>Va rugam sa ne spuneti daca va mai putem furniza si alte detalii....</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="da_oferim_informatia_de_care" target="6" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Da ( oferim informatia de care are nevoie )</text>
          <value />
        </answer>
        <answer id="1" key="_g2adhjvk8t5" target="6" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>Nu</text>
          <value />
        </answer>
      </answers>
    </question>
    <question id="6" key="multumim_pentru_timpul_acordat_zi" answerRequired="false" visible="true">
      <text>Multumim pentru timpul acordat.
O zi frumoasa!</text>
      <answers default_target="-1" answer_type="1">
        <answer id="0" key="end" target="-1" option_group="1" type="text" datasource="" selected="false" required="false" score="0">
          <triggers />
          <text>END!</text>
          <value />
        </answer>
      </answers>
    </question>
  </questions>
</survey>'
, @docHandle int 

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument

SELECT * FROM OPENXML (@docHandle, '/survey/questions/question/answers/answer', 2)
WITH (
      Question  VARCHAR(500) '../../text',
      selected  varchar(10) '@selected',
      Answer    VARCHAR(500)    'text'
      )
WHERE selected = 'true'
EXEC sp_xml_removedocument @docHandle 

more details can be found there: https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-openxml?view=sql-server-ver15