XML select query

Question

Hi,

I have a xml string like below

declare @XML as xml='<Invoice>
<No>1001</No>
<Date>06/09/2022</Date>
<Item>
<ItemNo>Item1</ItemNo>
<Price>100</Price>
<Qty>2</Qty>
</Item>
<Item>
<ItemNo>Item2</ItemNo>
<Price>200</Price>
<Qty>10</Qty>
<Extra>
<CommentType>Type1</CommentType>
<CommentValue>Value1</CommentValue>
</Extra>
<Extra>
<CommentType>Type2</CommentType>
<CommentValue>Value2</CommentValue>
</Extra>
</Item>
</Invoice>'

select @XML ;

i used the below code to get whichever item node having <Extra> tag details. but it bring wrong values

select
c.value('(No/text())1','varchar(100)') as _No,
c.value('(Date/text())1','varchar(100)') as _Date,

a.value('(ItemNo/text())1','varchar(100)') as _Item,
a.value('(Price/text())1','varchar(100)') as _Price,
a.value('(Qty/text())1','varchar(100)') as _Qty,

o.value('(CommentType/text())1','varchar(100)') as CommentType,
o.value('(CommentValue/text())1','varchar(100)') as CommentValue

from @XML .nodes('//Invoice') as s(c)
CROSS APPLY c.nodes('//Item') as g(a)
CROSS APPLY a.nodes('//Extra') as h(o)

my expected output is below image

Thanks in Advance
Kasim

Answer 1

Hi @kasim mohamed ,

I have a xml string like below

XML data type is NOT a string.

Please try the following solution.

Notable points:

• It is better not to use XPath expressions like "//..." for performance reasons.
• XPath predicate 'Item[Extra]' selects Item XML elements that have Extra child elements

SQL

DECLARE @xml XML =  
N'<Invoice>  
	<No>1001</No>  
	<Date>06/09/2022</Date>  
	<Item>  
		<ItemNo>Item1</ItemNo>  
		<Price>100</Price>  
		<Qty>2</Qty>  
	</Item>  
	<Item>  
		<ItemNo>Item2</ItemNo>  
		<Price>200</Price>  
		<Qty>10</Qty>  
		<Extra>  
			<CommentType>Type1</CommentType>  
			<CommentValue>Value1</CommentValue>  
		</Extra>  
		<Extra>  
			<CommentType>Type2</CommentType>  
			<CommentValue>Value2</CommentValue>  
		</Extra>  
	</Item>  
</Invoice>';  

  
SELECT c.value('(No/text())[1]','varchar(100)') as _No,  
 c.value('(Date/text())[1]','varchar(100)') as _Date,  
  
 a.value('(ItemNo/text())[1]','varchar(100)') as _Item,  
 a.value('(Price/text())[1]','varchar(100)') as _Price,  
 a.value('(Qty/text())[1]','varchar(100)') as _Qty,  
  
 o.value('(CommentType/text())[1]','varchar(100)') as CommentType,  
 o.value('(CommentValue/text())[1]','varchar(100)') as CommentValue  
from @XML.nodes('/Invoice') as s(c)  
 CROSS APPLY c.nodes('Item[Extra]') as g(a)  
 CROSS APPLY a.nodes('Extra') as h(o);  

Output

+------+------------+-------+--------+------+-------------+--------------+  
| _No  |   _Date    | _Item | _Price | _Qty | CommentType | CommentValue |  
+------+------------+-------+--------+------+-------------+--------------+  
| 1001 | 06/09/2022 | Item2 |    200 |   10 | Type1       | Value1       |  
| 1001 | 06/09/2022 | Item2 |    200 |   10 | Type2       | Value2       |  
+------+------------+-------+--------+------+-------------+--------------+