Numbering an xml block

,
1

how can i configure a unique number for each block while querying xml file with sql.
for example i would like to configure that the block with the 1 unit is block number 1
the block with the 5 units is block number 2

Red

1
40


5
80

2

Your XML block does not show in your posting (which you probably already knew). Likely that it is not your fault, it is the forum software doing funny things to you. This is one of those rare cases where you might be better of posting a screen-shot of the XML.

3

I think if you edit your O/P (or repost it) using TAGs that should work:

    ```text

    Your XML here

    ```
4

image
image.png354×553 15.2 KB

i would like to give a unique number to each Product and Sales nodes
is there is a way to do it?

5

Like in the example below:

DECLARE @x XML = 
'<Products>
	<Product>
		<ProductName>TV</ProductName>
		<Sales>
			<Income>222</Income>
		</Sales>
		<Sales>
			<Income>333</Income>
		</Sales>
	</Product>
	<Product>
		<ProductName>Laptop</ProductName>
		<Sales>
			<Income>1234</Income>
		</Sales>
		<Sales>
			<Income>5678</Income>
		</Sales>
	</Product>
</Products>';

SELECT
	ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS UniqueProductNumber,
	c1.value('ProductName[1]','VARCHAR(64)') AS ProductName,
	ROW_NUMBER() OVER (PARTITION BY c1.value('ProductName[1]','VARCHAR(64)') ORDER BY (SELECT NULL)) AS SalesNumber,
	c2.value('Income[1]','FLOAT') AS Income
FROM
	@x.nodes('//Product') T1(c1)
	OUTER APPLY c1.nodes('Sales') T2(c2);

BTW, I am using @Kristen's suggestion for presenting the XML.

6

thanks for the reply
your string numbers the elements with values like "ProductName" and "Income"
my problem is to number only the block "Product" and the block "Sales"