XML select query

kasim mohamed 581 Reputation points
2022-09-06T15:49:03.423+00:00

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

238283-image.png

Thanks in Advance
Kasim

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,630 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,926 Reputation points
    2022-09-06T16:49:31.66+00:00

    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       |  
    +------+------------+-------+--------+------+-------------+--------------+  
    
    2 people found this answer helpful.
    0 comments No comments