CODEWITHSUNDEEP
sqlsql-serverxml
MohammedS
MohammedS

Reputation: 221

XML formatting in SQL Server xml path

I am trying to get a particular XML format by querying SQL Server.

My nested query looks like this:

SELECT 
    @xml = (SELECT 
                AdmissionID AS "@encounter_number",
                'NDIS' AS organisation_code,
                'NDIS' AS encounter_type,
                (SELECT 'true' as "@delete",
                    (SELECT 'NDIS' AS class_type, AdmissionDate AS start_date_time 
                     FOR XML PATH ('financial_class'), TYPE) 
                 FOR XML PATH('financial_classes'), TYPE),
                (SELECT URNumber AS 'payor/@entity_number', 'Patient' AS slot, CONVERT (varchar(10), AdmissionDate, 126) AS start_date 
                 FOR XML PATH('payor'), ROOT ('payors'), ELEMENTS, TYPE),
                URNumber AS "recipient/@entity_number",
                'H' AS referral_source,
                'H' AS separation_status,
                AdmissionDate AS start_date_time
            FROM 
                HMS
            FOR XML PATH('encounter'), ROOT ('pbrc'), TYPE)

which returns this output:

<pbrc>
  <encounter encounter_number="525241">
    <organisation_code>NDIS</organisation_code>
    <encounter_type>NDIS</encounter_type>
    <financial_classes delete="true">
      <financial_class>
        <class_type>NDIS</class_type>
        <start_date_time>2018-06-26T00:00:00</start_date_time>
      </financial_class>
    </financial_classes>
    <payors>
      <payor>
        <payor entity_number="4208151" />
        <slot>Patient</slot>
        <start_date>2018-06-26</start_date>
      </payor>
    </payors>
    <recipient entity_number="4208151" />
    <referral_source>H</referral_source>
    <separation_status>H</separation_status>
    <start_date_time>2018-06-26T00:00:00</start_date_time>
  </encounter>
</pbrc>

But I am trying to achieve the following format for payor with proper payor closing tag and not the short form, 

-<payors>
 -<payor>
 <payor entity_number="8195991"> </payor>

 <slot>Patient</slot>

 <start_date>2019-06-01</start_date>

 </payor>

</payors>

Any suggestion please?

Upvotes: 0

Views: 86

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

This was a comment, but I shifted it into the answer:

Are you aware of the fact, that - semantically - there's no difference between <SomeElement /> and <SomeElement></SomeElement>? Is the blank between <payor> and </payor> important? In fact, it is up to the presenting engine, how this will be looking. The same XML might appear in different shape depeding on the viewer...

You can try something along this:

DECLARE @mockPayor TABLE(enitityNumber INT,slot VARCHAR(100));
INSERT INTO @mockPayor VALUES(123,'slot123')

--This will return the self-closed payor element

SELECT p.enitityNumber AS [payor/@entityNumber]
      ,p.slot
FROM @mockPayor p
FOR XML PATH('payor')

The result

<payor>
  <payor entityNumber="123" />
  <slot>slot123</slot>
</payor>

And this will return the proper payor closing tag:

SELECT p.enitityNumber AS [payor/@entityNumber]
      ,' ' AS [payor]
      ,p.slot
FROM @mockPayor p
FOR XML PATH('payor');

The result

<payor>
  <payor entityNumber="123"> </payor>
  <slot>slot123</slot>
</payor>

Some background:

The engine works this down and thinks:

  • Okay, there is an entityNumber attribute living within a payor element. Well I have to open the payor element and insert the entitiyNumber.
  • Now there is a slot element. So I have to close the payor without any content and open the slot element.

In the second statement the engine thinks:

  • Same as above
  • Now there is some content to be placed into the payor element
  • Same as above, but replace the bold part with after the content

Upvotes: 1

Related Questions