Parse XML data in SQL Server table

Question

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

2 revs · Accepted Answer · 2022-01-12 11:59:58Z

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

Collectives™ on Stack Overflow

Parse XML data in SQL Server table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related