How do I control how many Item Elements <Item> are in the element <Items>?

Category: sql server xml

Question

RPCASEY001 on Fri, 16 Jan 2015 23:27:47


If I have a query using FOR XML PATH that has a schema like...

<Items>

   <Item>

   </Item>

   <Item>

   </Item>

   ...

</Items>

How do I control how many Item Elements <Item> are in the element <Items>?

I need to only have 100 Item Elements <Item> per Items Element <Items>. If there are more that 100 Item Elements <Item>, I need to start a new Items Element <Items> and continue where the last Item Element <Item> left off in the previous Items Element <Items>.

Is this possible?


Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

Replies

Erland Sommarskog on Sat, 17 Jan 2015 11:45:09


You need to run a FOR XML PATH('') query for each group of 100, so it will become a bit messy.

Here is an example where I have used 5 as the limit instead of 100 to make it easier to test.

WITH quintuples AS (
   SELECT DISTINCT column_id AS start
   FROM   sys.columns
   WHERE  column_id % 5 = 1
)
SELECT o.name AS Tablename, q.ColumnNodes
FROM   sys.objects o
CROSS  APPLY  (SELECT d.ColumnNode
               FROM   quintuples q
               CROSS  APPLY (SELECT c.name AS [@Columnname],
                                    type_name(c.system_type_id) AS [@Typename]
                             FROM   sys.columns c
                             WHERE  c.object_id = o.object_id
                               AND  c.column_id BETWEEN q.start AND q.start + 5
                             FOR  XML PATH('Column'), TYPE) AS d (ColumnNode)
               ORDER  BY q.start
               FOR XML PATH(''), TYPE) AS q(ColumnNodes)
ORDER BY o.name
FOR XML PATH('Table'), ROOT('AllTables'), TYPE

RPCASEY001 on Sat, 17 Jan 2015 11:58:52


This is great!

Is the a Common Table Expression (CTE)? Is it a recursive CTE?

Would you please take a moment to explain what is going on here?

What is CROSS APPLY and why is it used twice?

I have read about TYPE, but the technical description did not help me understand why I use it.

For instance, I have  a query with nested selects to get a parent child hierarch in the XML and the nested select queries use TYPE. I only used type because the sample code I found used type. However, in the sample code, the last FOR XML did not have TYPE, so mu SQL does not have TYPE on the last FOR XML. Why are you adding the TYPE to the last FOR XML (FOR XML PATH('Table'), ROOT('AllTables'), TYPE)?

In the first expression (I think it is a CTE), what is going on with the %5=1? Is this a MOD (remainder)?

I am  new to XML so your help is greatly appreciated.

Thanks you so much!

Ryan

Erland Sommarskog on Sat, 17 Jan 2015 15:55:02


The thing that starts with WITH is indeded a CTE, a Common Table Expression. It is not a recursive CTE. A CTE is a query-scoped view. It's purely a logical construct, which permits you to reference the same query expression more than once in the query. It is not materialised. Instead the algebrizer expands the CTE with its definition in all places.

The % is a modulus operator. To be able to drive the query, we need the starting points 1, 6, 11 and so on from the source. That is what the CTE achieves.

CROSS APPLY is a sort of JOIN operator, where the right side may reference columns from the left side. You often use CROSS APPLY with FOR XML PATH to produce XML documents with hierarchies.

The purposes of ,TYPE is to define the data type of the XML document. By default, FOR XML produces nvarchar(MAX), you need to use ,TYPE to get the xml data type. (This is due to legacy. FOR XML was introduced in SQL 2000, but the xml data type was not added until SQL 2005.) You need to use ,TYPE for the nested XML documents, but for the outermost, it is not that important. (Then again, it's not bad practice to add it.)

If you are not used to CTEs and does not know CROSS APPLY and also is new to FOR XML PATH, the query I gave is certainly not trivial. On top of all I took the example from the catalog views in SQL Server, that you may not be acquainted with. But you also asked for something quite advanced. And you gave me no table definition or sample data to work with, so I had to use something that was available.

Here is a simpler query, which does not meet your requirements, but which demonstrates you can build a multi-level XML document. Not that I know if that is in your requirement, but it may help you to build your query.

SELECT o.name AS Tablename, d.ColumnNode
FROM   sys.objects o
CROSS  APPLY  (SELECT c.name AS [@Columnname], column_id AS [@column_id],
                      type_name(c.system_type_id) AS [@Typename]
               FROM   sys.columns c
               WHERE  c.object_id = o.object_id
               ORDER  BY c.column_id
               FOR  XML PATH('Column'), TYPE) AS d (ColumnNode)
ORDER BY o.name
FOR XML PATH(''), TYPE

I should add that there may be better to ways to get what you ask for than the query that I posted. We'll see if anyone else has any suggestions.

RPCASEY001 on Mon, 19 Jan 2015 21:51:11


One last simple question...

In the last simple example you provided to demonstrate multi-level XML documents, I noticed the last line uses FOR XML PATH(''), TYPE.

When can use PATH('') with the parenthesis have an empty string '' and when do I have to use a real string such as Path('RealString')?

I tried using path with an empty string and I got an error message.

--- Ryan

RPCASEY001 on Mon, 19 Jan 2015 21:54:24


The error I get when I use an empty string PATH('') in my path is as follows:

Msg 6864, Level 16, State 1, Line 1

Row tag omission (empty row tag name) cannot be used with attribute-centric FOR XML serialization.

RPCASEY001 on Mon, 19 Jan 2015 21:55:02


What is an attribute centric FOR XML serialization?

Erland Sommarskog on Mon, 19 Jan 2015 22:46:07


In the last simple example you provided to demonstrate multi-level XML documents, I noticed the last line uses FOR XML PATH(''), TYPE.

When can use PATH('') with the parenthesis have an empty string '' and when do I have to use a real string such as Path('RealString')?

I think the best answer is that you try to put in a string in PATH and see what happens.

What is an attribute centric FOR XML serialization?

This is attribute-centric:

    <ColumnNode>
        <Column Columnname="oplsn_fseqno" column_id="1" Typename="int" />
        <Column Columnname="oplsn_bOffset" column_id="2" Typename="int" />
        <Column Columnname="oplsn_slotid" column_id="3" Typename="int" />
        <Column Columnname="file_id" column_id="4" Typename="int" />
        <Column Columnname="rowset_guid" column_id="5" Typename="uniqueidentifier" />
        <Column Columnname="column_guid" column_id="6" Typename="uniqueidentifier" />
        <Column Columnname="filestream_value_name" column_id="7" Typename="nvarchar" />
        <Column Columnname="transaction_sequence_num" column_id="8" Typename="bigint" />
        <Column Columnname="status" column_id="9" Typename="bigint" />
        <Column Columnname="size" column_id="10" Typename="bigint" />
    </ColumnNode>

This is in opposition to element-centric XML:

    <ColumnNode>
        <Column>
             <Columnname>oplsn_fseqno</Columnname>
             <column_id>1</column_id>
             <Typename>int</Typename>
        </Column>
        <Column>
        ...
    </ColumnNode>
 

RPCASEY001 on Mon, 19 Jan 2015 22:55:55


So, what statements in your example make your SQL element-centric and what statements in my example make my SQL attribute-centric? See your example SQL and my example SQL below:

Your Example SQL:

SELECT o.name AS Tablename, d.ColumnNode
 FROM   sys.objects o 
 CROSS  APPLY  (SELECT c.name AS [@Columnname], column_id AS [@column_id],
                       type_name(c.system_type_id) AS [@Typename]
                FROM   sys.columns c
                WHERE  c.object_id = o.object_id
                ORDER  BY c.column_id
                FOR  XML PATH('Column'), TYPE) AS d (ColumnNode)
 ORDER BY o.name
 FOR XML PATH(''), TYPE

My Example SQL:

SELECT '0000' + CAST(store.[StoreNumber] AS CHAR(4)) AS '@ShipNode', 'Y' AS '@ApplyDifferences',
       (SELECT
             'GS_US' AS '@InventoryOrganizationCode',
             s2.[ProductID] AS '@ItemID',
             '' AS '@ProductClass',
             'Each' AS '@UnitOfMeasure'
                    ,       (SELECT
             s3.[QtyOnHand] AS '@Quantity',
             'OnHand' AS '@SupplyType',
             'Track' AS '@SupplyAvailabilty'
             FROM [StoreInformation].[dbo].[StoreInventory] s3
                    WHERE s3.StoreInventoryID = s1.storeinventoryid
             FOR XML PATH('Supply'), TYPE) AS 'Supplies'
             FROM [StoreInformation].[dbo].[StoreInventory] s2
                    WHERE s2.StoreInventoryID = s1.storeinventoryid
             FOR XML PATH('Item'), TYPE) AS 'Items'
FROM [StoreInformation].[dbo].[StoreInventory] s1
JOIN [StoreInformation].[dbo].[Store] store
ON s1.StoreID = store.StoreID
WHERE s1.StoreID = 9609
FOR XML PATH('Inventory')


Erland Sommarskog on Tue, 20 Jan 2015 21:59:39


Your Example SQL:

SELECT o.name AS Tablename, d.ColumnNode
 FROM   sys.objects o
 CROSS  APPLY  (SELECT c.name AS [@Columnname], column_id AS [@column_id],
                       type_name(c.system_type_id) AS [@Typename]
                FROM   sys.columns c
                WHERE  c.object_id = o.object_id
                ORDER  BY c.column_id
                FOR  XML PATH('Column'), TYPE) AS d (ColumnNode)
 ORDER BY o.name
 FOR XML PATH(''), TYPE

This is attribute-centric. Take out the cinnamon buns, eh, I mean the @
characters, and you will get element-centric.

RPCASEY001 on Tue, 20 Jan 2015 23:12:37


That makes sense.

Thank you so much!

--- Ryan