How to query xml from xml variable?

Hello Experts,

I have a xml variable called @xmlstr in below code snippet.  

I need to extract customer xml portion (as mentioned below) from OrderInfo xml and store it in a table.

<Customer>
            <Name>First, Last</Name>
            <Address>xyz, zip</Address>
</Customer>



Please help me how can i extract sub-xml portion from xml?


Regards,
itsvtk
declare @xmlstr xml
set @xmlstr = 
'<OrderInfo>
	<Customer>
		<Name>First, Last</Name>
		<Address>xyz, zip</Address>
	</Customer>
	<Product>
		<Number>xyz123</Number>
		<Name>Product Name</Name>
	</Product>
</OrderInfo>'

Open in new window

LVL 14
Thandava VallepalliAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Select @xmlstr.query('(/OrderInfo/Customer)[1]')
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT A.Customers.value('(Customer/Name)[1]','varchar(40)') name,
         A.Customers.value('(Customer/Address)[1]','varchar(100)') Address,
         A.Customers.value('(Product/Number)[1]','varchar(100)') ProductNumber,
         A.Customers.value('(Product/Name)[1]','varchar(100)') ProductName
FROM @xmlstr.nodes('/OrderInfo') A(Customers)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.