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.

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

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

declare @xmlstr xml
set @xmlstr = 
		<Name>First, Last</Name>
		<Address>xyz, zip</Address>
		<Name>Product Name</Name>

Open in new window

LVL 14
Thandava VallepalliAsked:
Who is Participating?
Anthony PerkinsConnect With a Mentor Commented:
Select @xmlstr.query('(/OrderInfo/Customer)[1]')
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)
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.