navigating XML: find the collection based on the value in one of its nodes

Question

Post reply

navigating XML: find the collection based on the value in one of its nodes

aaron.reese

SSChampion

Points: 13498

October 14, 2022 at 11:22 am

#4099557

I struggle with XQuery and Xpath, I am sure the following is possible but I can't work out how to do it.

Given the XML structure:

DECLARE @MAPRules XML = N'
<root>
  <email>userEmail</email>
  <burst>user_name</burst>
  <subject>
    <field>
      <name>reportDate</name>
      <value>19th October 2023</value>
    </field>
  </subject>
  <collections>
    <collection>
      <collectionname>properties</collectionname>
      <groupby>DomainRef</groupby>
      <instancename>property</instancename>
      <fields>
        <field>
          <name>propertyref</name>
          <column>DomainRef</column>
        </field>
        <field>
          <name>locationtype</name>
          <column>location_type</column>
        </field>
        <field>
          <name>address</name>
          <column>Address1</column>
        </field>
        <field>
          <name>postcode</name>
          <column>PostCode</column>
        </field>
        <field>
          <name>bedspaces</name>
          <collection>bedspaces</collection>
        </field>
     </fields>
  </collection>
  <collection>
    <collectionname>bedspaces</collectionname>
    <groupby>RoomNumber</groupby>
    <instancename>bedspace</instancename>
    <fields>
        <field>
          <name>room</name>
          <column>RoomNumber</column>
        </field>
        <field>
          <name>address</name>
          <column>Address1</column>
        </field>
      </fields>
    </collection>
  </collections>
</root>
'

I would like a query to find the <collection> and all of its nested path details based on the value in the <groupby>. So if I pass it 'RoomNumber' it will return me the data for the 2nd collection and if I pass it 'DomainRef' it will return me the first collection.

For context, I am trying to convert a flat data set (which has repeated data for outer grouped items) into a nested XML data set. The MapRules tells me which fields from the flat data set should be included in the XML collection and what they should be called in the XML data set. The data set and XML output are not known in advance so this intermediate MAPRules specifies the how to carry out the conversion as a generic process. In this particular example I will have a collection of <properties> (each instance being identified as property) and within each <property> will be a collection of <bedspaces> (instance = <bedspace>). I am pretty sure I can code this if I can just work out how to get into the correct collections based on the groupby value

<properties>
  <property>
    <propertyref>1234</propertyref>
    <locationtype>BLOCK</locationtype>
    <address>32 Acacia Road</address>
    <postcode>AB1 1AB</postcode>
    <bedspaces>
      <bedspace>
        <room>Room 1</room>
        <address>32 Acacia Road</address>
      </bedspace>
      <bedspace>
        <room>Room 2</room>
        <address>32 Acacia Road</address>
      </bedspace>
      <bedspace>
        <room>Room 3</room>
        <address>32 Acacia Road</address>
      </bedspace>
    </bedspaces>
  </property>
  <property>
    <propertyref>4567</propertyref>
    <locationtype>CONVERTED</locationtype>
    <address>17 Hampton Court</address>
    <postcode>ZY7 8AZ</postcode>
    <bedspaces>
      <bedspace>
        <room>Room 1</room>
        <address>17 Hampton Court</address>
      </bedspace>
      <bedspace>
        <room>Room 2</room>
        <address>17 Hampton Court</address>
      </bedspace>
      <bedspace>
        <room>Room 3</room>
        <address>17 Hampton Court</address>
      </bedspace>
    </bedspaces>
  </property>
</properties>

This topic was modified 2 years ago by aaron.reese.
This topic was modified 2 years ago by aaron.reese.
This topic was modified 2 years ago by aaron.reese.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

aaron.reese SSChampion Points: 13498 More actions · Answer 1

I love answering my own questions. The syntax is:

SELECT @MapRules.query('/root/collections/collection[groupby = "RoomNumber"]')

This give you the inner XML for the collection which I can convert to a SQL table and iterate through.

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 2

Just a quick thought, here is a slightly more efficient way 😉

😎

SELECT
    COLL.DATA.query('.')
FROM @MapRules.nodes('/root/collections/collection') AS COLL(DATA)
WHERE COLL.DATA.value('(groupby/text())[1]','NVARCHAR(50)') = 'RoomNumber';

Reduces the query's work by ca. 50%