Parse XML data in SQL Server table-CodePudding

I was studing a little about xml parsing, using examples I found here in stackoverflow but I came across a doubt. If I have something like this

DECLARE @xml xml
SET @xml = 
'<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order>   
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
         </oo_master> 
    </oo_outbound_order>'

SELECT 
    n.value('(./Code/text())[1]','int') as CODE
 , n.value('(./Name/text())[1]','Varchar(50)') as NAME
FROM @xml.nodes('/oo_outbound_order/oo_master') as a(n)

I get something like this

CODE    NAME
123     Branan

but if one of the fields I want to store it as xml

<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order> 
        <Active>true</Active>
        <Weight>0.02</Weight>
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
        </oo_master>
    </oo_outbound_order>

And I want to get something like this

 Active   Weight    ColWithXML
  true     0.02     <oo_master><Code>123</Code><Name>Branan</Name></oo_master>


SELECT 
    n.value('Active[1][not(@xsi:nil = "true")]', 'BIT') as Active
 , n.value('Weight[1][not(@xsi:nil = "true")]', 'DECIMAL(29,5)') as Weight
, ??????????????????

How can I do that? Thank you

CodePudding user response：

This answer was provided in the comments my lptr, and it answered the OP's question. To preserve the usefulness of the comments, as they can be deleted at any time, I have posted it as an answer.

Here they (lptr) uses the query method of the xml data type (query() Method (xml Data Type)) to achieve your goal. They used 2 different, but similar similar solutions:

a.n.query('.')

DECLARE @xml xml
SET @xml = 
'<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order>   
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
         </oo_master> 
    </oo_outbound_order>'

SELECT 
    n.value('(./Code/text())[1]','int') as CODE
 , n.value('(./Name/text())[1]','Varchar(50)') as NAME
 ,a.n.query('.')
FROM @xml.nodes('/oo_outbound_order/oo_master') as a(n)

db<>fiddle

..or n.query('oo_master') FROM @xml.nodes('/oo_outbound_order')

DECLARE @xml xml
SET @xml = 
'<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order>   
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
         </oo_master> 
    </oo_outbound_order>'

SELECT 
    n.value('(./Code/text())[1]','int') as CODE
 , n.value('(./Name/text())[1]','Varchar(50)') as NAME
 ,a.n.query('.')
FROM @xml.nodes('/oo_outbound_order/oo_master') as a(n);


select @xml ='<?xml version="1.0" encoding="UTF-8"?> 
<oo_outbound_order> 
        <Active>true</Active>
        <Weight>0.02</Weight>
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name> 
        </oo_master>
    </oo_outbound_order>';
  
SELECT 
n.value('Active[1][not(@xsi:nil = "true")]', 'BIT') as Active
 , n.value('Weight[1][not(@xsi:nil = "true")]', 'DECIMAL(29,5)') as Weight
 , a.n.query('./oo_master')
FROM @xml.nodes('/oo_outbound_order') as a(n);

db<>fiddle