none
Insert XML into SQL

    질문

  • Hi all,

    I need to insert data from an XML document into a field in a SQL table. This is what I have.

    Stored Procedure

    ALTER PROCEDURE [dbo].[spImport]

    @XML asxml

    AS

    BEGIN

          DECLARE@ProcDate datetime

          SET@ProcDate =GetDate()

          INSERTINTOQueue (XMLValue,ProcDate)VALUES (@XML,@ProcDate)

    END

    The XML looks like this.

    <?xml version="1.0" encoding="utf-8" standalone="yes"?>
    <Table>
      <Product>
        <Product_id>1</Product_id>
        <ProductName>Product 1</ProductName>
        <ProductPrice>1000</ProductPrice>
      </Product>
      <Product>
        <Product_id>2</Product_id>
        <ProductName>Product 2</ProductName>
        <ProductPrice>2000</ProductPrice>
      </Product>
      <Product>
        <Product_id>3</Product_id>
        <ProductName>Product 3</ProductName>
        <ProductPrice>3000</ProductPrice>
      </Product>
      <Product>
        <Product_id>4</Product_id>
        <ProductName>Product 4</ProductName>
        <ProductPrice>4000</ProductPrice>
      </Product>
    </Table>

    I want to use LINQ To SQL and I am getting the error below.

    Error 1 Value of type 'System.Xml.Linq.XContainer' cannot be converted to 'MyProject.WebService.XContainer'. How can I insert all the product nodes into the XML field?

    Thanks in advance.

    2012년 3월 12일 월요일 오후 7:56

답변

  •  Not sure about LINQ so you may want to try a LINQ forum.  However this document indicates the SQL Server XML datatype maps to System.Xml.Linq.XElement.

    That being true, you should be able to pass your XML straight in, although I haven't checked this.  If you also only want the Product elements and not the surrounding Table element then do something like below, but bear in mind well-formed XML should have a single root element:

    ALTER PROCEDURE [dbo].[spImport]
    
    	@xml AS XML
    
    AS
    
    BEGIN
    
    	SET NOCOUNT ON
    
    	INSERT INTO Queue ( XMLValue,ProcDate )
    	SELECT GETDATE(), @xml.query('Table/Product')
     
    END

    • 답변으로 표시됨 KJian_ 2012년 3월 20일 화요일 오전 2:23
    2012년 3월 12일 월요일 오후 9:00

모든 응답

  •  Not sure about LINQ so you may want to try a LINQ forum.  However this document indicates the SQL Server XML datatype maps to System.Xml.Linq.XElement.

    That being true, you should be able to pass your XML straight in, although I haven't checked this.  If you also only want the Product elements and not the surrounding Table element then do something like below, but bear in mind well-formed XML should have a single root element:

    ALTER PROCEDURE [dbo].[spImport]
    
    	@xml AS XML
    
    AS
    
    BEGIN
    
    	SET NOCOUNT ON
    
    	INSERT INTO Queue ( XMLValue,ProcDate )
    	SELECT GETDATE(), @xml.query('Table/Product')
     
    END

    • 답변으로 표시됨 KJian_ 2012년 3월 20일 화요일 오전 2:23
    2012년 3월 12일 월요일 오후 9:00
  • Hi,

    You can use OPENXML also,

    rough example

    select one,two from openxml ('//Root') (one varchar(12), two varchar(12))).....

    i am sure OPENXML can do the job

    2012년 3월 15일 목요일 오전 10:00