Can't insert complex XML file into SQL Server table

Question

I have a stored procedure like below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Sp_ComplexXml]
AS
BEGIN
    DECLARE @XmlString xml

    SET @XmlString = 
    '<ArrayOfSizeColorQuantityViewModel>
      <ProductId>012312</ProductId>
      <SizeColorQuantityViewModel>
        <ColorId>Red</ColorId>
        <SizeAndQuantities>
          <SizeAndQuantity>
            <SizeId>S</SizeId>
            <Quantity>1</Quantity>
          </SizeAndQuantity>
          <SizeAndQuantity>
            <SizeId>M</SizeId>
            <Quantity>2</Quantity>
          </SizeAndQuantity>
          <SizeAndQuantity>
            <SizeId>L</SizeId>
            <Quantity>3</Quantity>
          </SizeAndQuantity>
          <SizeAndQuantity>
            <SizeId>XL</SizeId>
            <Quantity>4</Quantity>
          </SizeAndQuantity>
        </SizeAndQuantities>
      </SizeColorQuantityViewModel>

      <SizeColorQuantityViewModel>
        <ColorId>Blue</ColorId>
        <SizeAndQuantities>
          <SizeAndQuantity>
            <SizeId>S</SizeId>
            <Quantity>1</Quantity>
          </SizeAndQuantity>
          <SizeAndQuantity>
            <SizeId>M</SizeId>
            <Quantity>2</Quantity>
          </SizeAndQuantity>
          <SizeAndQuantity>
            <SizeId>L</SizeId>
            <Quantity>3</Quantity>
          </SizeAndQuantity>
          <SizeAndQuantity>
            <SizeId>XL</SizeId>
            <Quantity>4</Quantity>
          </SizeAndQuantity>
        </SizeAndQuantities>
      </SizeColorQuantityViewModel>
    </ArrayOfSizeColorQuantityViewModel>'

    INSERT INTO ProductSizeColor(ProductID, SizeID, ColorID, Quantity)
        SELECT
            SCQ.node.value('(../ProductId)[1]', 'bigint') as 'ProductID',
            SCQ.node.value('(ColorId)[1]', 'varchar(3)') as 'ColorID',
            SQ.i.value('(SizeId)[1]', 'varchar(3)') as 'SizeID',
            SQ.i.value('(Quantity)[1]', 'int') as 'Quantity'
        FROM
            @XmlString.nodes('/ArrayOfSizeColorQuantityViewModel/SizeColorQuantityViewModel') as SCQ(node)
        CROSS APPLY 
            SCQ.node.nodes('/SizeAndQuantities/SizeAndQuantity') as SQ(i)
END

And I try to insert this complex XML data to table below:

Table: (3 foreign keys)

I don't know whether I have any problem with parse XML code or not? If you don't mind could you please help me find it out?

Thanks for kind helping

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Aug 31, 2016 at 20:24

marc_s · Accepted Answer · 2016-08-31 20:27:35Z

1

Try this code:

SELECT
    SCQ.node.value('(../ProductId)[1]', 'bigint') as 'ProductID',
    SCQ.node.value('(ColorId)[1]', 'varchar(4)') as 'ColorID',
    SQ.i.value('(SizeId)[1]', 'varchar(3)') as 'SizeID',
    SQ.i.value('(Quantity)[1]', 'int') as 'Quantity'
FROM
    @XmlString.nodes('/ArrayOfSizeColorQuantityViewModel/SizeColorQuantityViewModel') as SCQ(node)
CROSS APPLY 
    SCQ.node.nodes('SizeAndQuantities/SizeAndQuantity') as SQ(i)

I just removed a leading / from your second XPath (in the CROSS APPLY), and I also extended the length of the ColorId value to varchar(4) (to allow for "blue" to be shown properly).

Now I get this output from the SELECT:

answered Aug 31, 2016 at 20:27

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Long Nguyen Over a year ago

It works like a charm. Thanks for your kind helping :D

Collectives™ on Stack Overflow

Can't insert complex XML file into SQL Server table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related