无法将复杂的XML文件插入到SQL Server表中

Question

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

Answer 1

尝试以下代码：

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)

我刚刚从第二个XPath中删除了前导的/（在交叉应用中），并将colorid值的长度扩展为varchar(4)（以便正确显示“blue”）。

现在，我从select中获得以下输出：