XML in TSQL: looping through each child node and insert them-CodePudding

I have a SQL table receiving orders

<XMLGateway>
  <Header>
    ....
  </Header>
  <Body>
    <Orders>
      <Order>
        <ItemCode>315689</ItemCode>
        <ProductName>Item1</ProductName>
      </Order>
      <Order>
        <ItemCode>123456</ItemCode>
        <ProductName>Product 1</ProductName>
      </Order>
    </Orders>
  </Body>

I would then like to iterate through each order and insert them separately into my Orders table

insert into orders (ItemCode,ProductName) as separate records

Is there a simpler solution than a cursor?

CodePudding user response：

You can try with this XQuery statement - no cursors needed for sure!

-- you have your XML in a SQL variable here 
DECLARE @input XML = '<XMLGateway>
  <Body>
    <Orders>
      <Order>
        <ItemCode>315689</ItemCode>
        <ProductName>Item1</ProductName>
      </Order>
      <Order>
        <ItemCode>123456</ItemCode>
        <ProductName>Product 1</ProductName>
      </Order>
    </Orders>
  </Body>
  </XMLGateway>'

-- Commented out the "INSERT" part, so you can try what the "SELECT" returns    
-- INSERT INTO dbo.Orders (ItemCode, ProductName)
SELECT
    -- pick out the "ItemCode" and "ProductName" subelements from the <Order> node
    ItemCode = XC.value('(ItemCode/text())[1]', 'int'),
    ProductName = XC.value('(ProductName/text())[1]', 'varchar(100)')
FROM
    -- get the "<Order>" XML nodes, as a list of XML fragments
    @input.nodes('/XMLGateway/Body/Orders/Order') AS XT(XC)