sql server 15 parse xml column returns null

Question

I want to make query xml value sql server and get auditingCompanyAddress value

create table sqm (data xml)

insert into sqm
select '<taxComplianceReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.gsis.gr/cpaaudit/v12">
<auditingGeneralInformation>
    <auditingHeader>
      <auditingCompany>
        <auditingCompanyTitle>ΣΥΝΕΡΓΑΖΟΜΕΝΟΙ ΟΡΚΩΤΟΙ ΛΟΓΙΣΤΕΣ Α.Ε. </auditingCompanyTitle>
        <auditingCompanyRegisterNum>125</auditingCompanyRegisterNum>
        <auditingCompanyVatNumber>094394659</auditingCompanyVatNumber>
        <auditingCompanyAddress>Φ. ΝΕΓΡΗ 3, 11257 ΑΘΗΝΑ</auditingCompanyAddress>
        <auditingCompanyFee>4000</auditingCompanyFee>
      </auditingCompany>
      <certifiedAccountant1>
        <certifiedAccountantsName>ΚΑΛΛΕΣ ΝΙΚΟΛΑΟΣ</certifiedAccountantsName>
        <certifiedAccountantsRegisterNum>1590</certifiedAccountantsRegisterNum>
        <accountantVatNumber>035209342</accountantVatNumber>
        <certifiedAccountantsCity />
        <certifiedAccountantsFee>0</certifiedAccountantsFee>
      </certifiedAccountant1>
      <disclaimer>true</disclaimer>
      <companyName>ΣΑΛΑΓΙΑΝΝΗΣ Γ.ΑΒΕΕ</companyName>
      <companyVatNumber>094357246</companyVatNumber>
      <periodFrom>2018-01-01</periodFrom>
      <periodTo>2018-12-31</periodTo>
      <fiscalYear>2018</fiscalYear>
      <conclusionReportVatCompliance>1</conclusionReportVatCompliance>
      <nonImportantDiffReportVatCompliance>2</nonImportantDiffReportVatCompliance>
      <pendingQuestions>false</pendingQuestions>
      <fiscalSubjectsNotAuditedDueToVatProblems>
        <exists>false</exists>
        <comments />
      </fiscalSubjectsNotAuditedDueToVatProblems>
    </auditingHeader>
  </auditingGeneralInformation>
  </taxComplianceReport>'

I am try to get with the following sql query:

select
    m.c.value('(auditingCompanyAddress)[1]', 'VARCHAR(max)') as auditingCompanyAddress
from sqm as s
    outer apply s.data.nodes('taxComplianceReport/auditingGeneralInformation/auditingHeader/auditingCompany') as m(c)

but returns null. I think the problem is the taxComplianceReport but I dont know how to resolve.

Any idea?

Thom A · Accepted Answer · 2020-11-26 13:26:24Z

1

There's a namespace in your XML yet you don't define it in your SQL. Define the DEFAULT one and it works:

WITH XMLNAMESPACES(DEFAULT'http://www.gsis.gr/cpaaudit/v12')
SELECT m.c.value('(auditingCompanyAddress)[1]', 'VARCHAR(max)') as auditingCompanyAddress
FROM sqm AS s
    OUTER APPLY s.data.nodes('taxComplianceReport/auditingGeneralInformation/auditingHeader/auditingCompany') AS m(c);

Note that, for me, this returns the varchar value 'F. ??G?? 3, 11257 ?T???' as (at least on my collation) a varchar cannot contain characters like Λ and Σ. If you get ?s as well, ensure you are using nvarchars.

answered Nov 26, 2020 at 13:26

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

sql server 15 parse xml column returns null

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