로그인
 
홈라이브러리학습다운로드지원커뮤니티포럼
Resources for IT Professionals >
> 
> 
Insert XML into SQL

Answered Insert XML into SQL

  • 2012년 3월 12일 월요일 오후 7:56
     
     
    로그인하여 투표
    0

    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일 월요일 오후 9:00
    답변자
     
     답변됨 코드 있음
    로그인하여 투표
    0

     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월 15일 목요일 오전 10:00
     
     
    로그인하여 투표
    0

    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