Inserting a XML document into a SQL table

Question

I am attempting to both create a table, with two columns, and to insert an XML document's data into the table. I am currently unable to get the code to run, so I am unsure of what could be causing issues. Any help would be greatly appreciated. Thank you.

CREATE TABLE TestProc(
TestProcID INT IDENTITY PRIMARY KEY,
Procedures XML,
);

DECLARE @TestProcXML xml;
SET @TestProcXML =
'
<TestProc>
<Proc>
<Description>the first procedure.</Description>
<SubProc>the first inner-proc.</SubProc>
<SubProc>the second inner-proc.</SubProc>
</Proc>
<Proc>
<Description>the second procedure.</Description>
</Proc>
<Proc>
<Description>the third procedure.</Description>
</Proc>
</TestProc>
'
INSERT INTO TestProc(Procedures);

Primary key declaration seems buggy. It should be TestProcID int IDENTITY(1,1) PRIMARY KEY, — Bertrand
– Bertrand, Commented Nov 30, 2018 at 0:24
It is not completely clear from the quest exactly what the error you are getting is. Actually including the error message will improve your question. I have taken a stab at providing an answer by giving you a 'corrected' version of your sample. If that doesn't solve your question you will need to give more information. — Mathew Paxinos
– Mathew Paxinos, Commented Nov 30, 2018 at 1:46
You are telling where to insert (insert something into table TestProc into the column Procedures, but forgot to tell the command the content to insert. As told in one answer you can either use VALUES or simply SELECT — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 30, 2018 at 12:03

marc_s · Accepted Answer · 2018-11-30 05:28:14Z

2

Although not completely clear on the issue, there are several elements of the code provided that could cause a failure.

I have provided a corrected version below. (NOTE: This was tested to work in MS SQL Server 2012)

CREATE TABLE TestProc
(
    TestProcID INT IDENTITY(1,1) PRIMARY KEY,
    Procedures XML,
);

DECLARE @TestProcXML xml;
SET @TestProcXML = '
    <TestProc>
        <Proc>
            <Description>the first procedure.</Description>
            <SubProc>the first inner-proc.</SubProc>
            <SubProc>the second inner-proc.</SubProc>
        </Proc>
        <Proc>
            <Description>the second procedure.</Description>
        </Proc>
        <Proc>
            <Description>the third procedure.</Description>
        </Proc>
    </TestProc>
    '

INSERT INTO TestProc(Procedures)
VALUES (@TestProcXML)

Specific fixes included are:

IDENTITY -> IDENTITY(1,1)
Added VALUES section to insert so there is data actually inserted into the table

edited Nov 30, 2018 at 5:28

marc_s

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

answered Nov 30, 2018 at 1:44

Mathew Paxinos

9647 silver badges16 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

The first fix is rather - uhm - not needed, while the second - quite probably - is the spot to push :-)

umesh99 · Accepted Answer · 2018-12-03 08:44:53Z

You can simply select the values when you inserting them into table just like below. This is the easiest way to inserting according to your scenario.

 CREATE TABLE TestProc(
    TestProcID INT IDENTITY(1,1) PRIMARY KEY,
    Procedures XML,
    );

    DECLARE @TestProcXML xml;
     SELECT @TestProcXML =
    '
    <TestProc>
    <Proc>
    <Description>the first procedure.</Description>
    <SubProc>the first inner-proc.</SubProc>
    <SubProc>the second inner-proc.</SubProc>
    </Proc>
    <Proc>
    <Description>the second procedure.</Description>
    </Proc>
    <Proc>
    <Description>the third procedure.</Description>
    </Proc>
    </TestProc>
    '


    INSERT INTO dbo.TestProc
            (Procedures)
    SELECT @TestProcXML

Collectives™ on Stack Overflow

Inserting a XML document into a SQL table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related