0

I have an XML code like this:

<report>
  <deltagere>
    <deltager>
      <number>142555267</number>
      <date>29-12-2006</date>
      <name>
        <name>
          <from>01-05-2000</from>
          <to>01-01-2003</to>
          <text>foo</text>
        </name>
        <name>
          <from>01-01-2003</from>
          <to>29-12-2006</to>
          <text>bzz</text>
        </name>
      </name>
      <information>
        <deltagertype>person</deltagertype>
        <leader>John Smith</leader>
        <status>Active</status>
      </information>
      <role>Responsible</role>
    </deltager>
    <deltager>
      <number>4000134982</number>
      <date>05-12-2007</date>
      <name>
        <name>
          <from>07-07-2007</from>
          <to>05-12-2007</to>
          <text>bar</text>
        </name>
      </name>
      <information>
        <deltagertype>person</deltagertype>
        <leader>Wolfgang Smith</leader>
        <status>Active</status>
      </information>
      <role>Responsible</role>
    </deltager>
    ...
  </deltagere>
</report>

Which I import just fine with this query:

SELECT
  number = deltagere.value('(number)[1]', 'bigint'),
  dato = deltagere.value('(date)[1]', 'varchar(10)'),
  nameFrom = XC.value('(from)[1]', 'varchar(10)'),
  nameTo = XC.value('(to)[1]', 'varchar(10)'),
  nameText = XC.value('(text)[1]', 'varchar(30)'),
  deltagertype = deltagere.value('(information/deltagertype)[1]', 'varchar(20)'),
  leader = deltagere.value('(information/leader)[1]', 'varchar(50)'),
  deltagerStatus = deltagere.value('(information/status)[1]', 'varchar(50)'),
deltagerRole = deltagere.value('(role)[1]', 'varchar(50)')
FROM
  @XmlFile.nodes('/report/deltagere/deltager') AS XTbl(deltagere)
CROSS APPLY
  deltagere.nodes('name/name') AS XT2(XC)

The problem is now, that not all the <deltager> nodes have <name> nodes. It could look something like this:

    <deltager>
      <number>1234134982</number>
      <date>05-12-2007</date>
      <information>
        <deltagertype>person</deltagertype>
        <leader>Wolfgang Smith</leader>
        <status>Active</status>
      </information>
      <role>Responsible</role>
    </deltager>

If the name node is missing, I want the name columns of that node to be filled with NULL. Like this:

| number     | dato       | nameFrom   | nameTo     | nameText | deltagertype | ...
-------------+------------+------------+------------+----------+--------------+ ...
| 1234134982 | 29-12-2006 | NULL       | NULL       | NULL     | person       | ...

I hope any of you experts have an idea on how to solve my problem.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Kasperhogh
  • 49
  • 7
  • Look at this [question](http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select), I hope it will help you – Oleks Apr 08 '15 at 08:50

1 Answers1

1

You can use OUTER APPLY instead of CROSS APPLY.

DECLARE @XmlFile XML
SET @XmlFile =
'
<report>
  <deltagere>
    <deltager>
      <number>142555267</number>
      <date>29-12-2006</date>
      <name>
        <name>
          <from>01-05-2000</from>
          <to>01-01-2003</to>
          <text>foo</text>
        </name>
        <name>
          <from>01-01-2003</from>
          <to>29-12-2006</to>
          <text>bzz</text>
        </name>
      </name>
      <information>
        <deltagertype>person</deltagertype>
        <leader>John Smith</leader>
        <status>Active</status>
      </information>
      <role>Responsible</role>
    </deltager>
    <deltager>
      <number>4000134982</number>
      <date>05-12-2007</date>
      <name>
        <name>
          <from>07-07-2007</from>
          <to>05-12-2007</to>
          <text>bar</text>
        </name>
      </name>
      <information>
        <deltagertype>person</deltagertype>
        <leader>Wolfgang Smith</leader>
        <status>Active</status>
      </information>
      <role>Responsible</role>
    </deltager>
    <deltager>
      <number>1234134982</number>
      <date>05-12-2007</date>
      <information>
        <deltagertype>person</deltagertype>
        <leader>Wolfgang Smith</leader>
        <status>Active</status>
      </information>
      <role>Responsible</role>
    </deltager>
  </deltagere>
</report>
'
SELECT
  number = deltagere.value('(number)[1]', 'bigint'),
  dato = deltagere.value('(date)[1]', 'varchar(10)'),
  nameFrom = XC.value('(from)[1]', 'varchar(10)'),
  nameTo = XC.value('(to)[1]', 'varchar(10)'),
  nameText = XC.value('(text)[1]', 'varchar(30)'),
  deltagertype = deltagere.value('(information/deltagertype)[1]', 'varchar(20)'),
  leader = deltagere.value('(information/leader)[1]', 'varchar(50)'),
  deltagerStatus = deltagere.value('(information/status)[1]', 'varchar(50)'),
deltagerRole = deltagere.value('(role)[1]', 'varchar(50)')
FROM
  @XmlFile.nodes('/report/deltagere/deltager') AS XTbl(deltagere)
OUTER APPLY
  deltagere.nodes('name/name') AS XT2(XC)

OUTPUT

number      dato        nameFrom    nameTo      nameText    deltagertype    leader          deltagerStatus  deltagerRole
142555267   29-12-2006  01-05-2000  01-01-2003  foo         person          John Smith      Active          Responsible
142555267   29-12-2006  01-01-2003  29-12-2006  bzz         person          John Smith      Active          Responsible
4000134982  05-12-2007  07-07-2007  05-12-2007  bar         person          Wolfgang Smith  Active          Responsible
1234134982  05-12-2007  NULL        NULL        NULL        person          Wolfgang Smith  Active          Responsible
Angus Chung
  • 1,547
  • 1
  • 11
  • 13