Query xml using xquery in SQL Server 2016

Question

I have my XML in following format:

<resultset xmlns="qm_system_resultset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <result>
    <result_id>F5</result_id>
    <exception>NO</exception>
    <recurring_count>0</recurring_count>
    <defect>NO</defect>
    <unresolved>NO</unresolved>
    <exception_approval />
    <comments />
    <exception_expiration>3000-01-01</exception_expiration>
    <exception_stat_only>NO</exception_stat_only>
    <result_data>
      <phraseprefix>rx</phraseprefix>
      <phrasenumber>0001</phrasenumber>
      <languagedesc>Khmer</languagedesc>
      <englishphrase> each time.</englishphrase>
      <phrasedesc> គ្រាប់ក្នុងមួយដង។</phrasedesc>
      <qm_translatedphrase>day.</qm_translatedphrase>
    </result_data>
  </result>
  <result>
    <result_id>26</result_id>
    <exception>NO</exception>
    <recurring_count>0</recurring_count>
    <defect>NO</defect>
    <unresolved>NO</unresolved>
    <exception_approval />
    <comments />
    <exception_expiration>3000-01-01</exception_expiration>
    <exception_stat_only>NO</exception_stat_only>
    <result_data>
      <phraseprefix>hold</phraseprefix>
      <phrasenumber>0001</phrasenumber>
      <languagedesc>Hmong</languagedesc>
      <englishphrase>Hold than 160.</englishphrase>
      <phrasedesc>Tsis 160.</phrasedesc>
      <qm_translatedphrase>Do not use </qm_translatedphrase>
    </result_data>
  </result>

Using TSQL/XML query how do I achieve this RESULT

[phraseprefix][phrasenumber]
    rx              0001
    hold            0001
    ...

I tried the following query, but I got null values for both the columns:

DECLARE @input XML = (SELECT result_xml
FROM QM_Data_Audit.QM_Package.test_results
WHERE result_id = 2446338)
SELECT 
resultset.value('(phraseprefix)[1]', 'varchar(max)') AS 'phrasenumber',
resultset.value('(phrasenumber)[1]', 'int') AS 'phrasenumber'
FROM @input.nodes('/resultset/result/result_data') AS List(resultset)

My apologies if the question is asked previously, I am new to querying XML. Appreciate your help.

B.Porter · Accepted Answer · 2018-04-02 21:30:59Z

1

Your xml has a namespace declared so you need to provide this when querying it. In this example this can be acheived with the WITH XMLNAMESPACES statement:

DECLARE @input XML = (SELECT result_xml
FROM QM_Data_Audit.QM_Package.test_results
WHERE result_id = 2446338);

WITH XMLNAMESPACES(DEFAULT 'qm_system_resultset')
SELECT 
 resultset.value('(phraseprefix)[1]', 'varchar(max)') AS 'phrasenumber',
 resultset.value('(phrasenumber)[1]', 'varchar(max)') AS 'phrasenumber'
FROM @input.nodes('/resultset/result/result_data') AS List(resultset)

You'll want to set the data type for phrasenumber to varchar as well to preserve the leading 0s if you need them.

edited Apr 2, 2018 at 21:30

answered Apr 2, 2018 at 21:24

B.Porter

3671 silver badge14 bronze badges

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

Collectives™ on Stack Overflow

Query xml using xquery in SQL Server 2016

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related