Insert XML data into SQL Server

Question

I am trying to insert "DocumentFields" values from XML into SQL Server 2008 but no luck.

Can you please help me with this? Below is the XML and the stored procedure used.

CREATE PROCEDURE [dbo].[SP_Test]
   (@xmlData As XML)
AS
BEGIN
    DECLARE @idoc int

    SET @xmlData='<?xml version="1.0" encoding="UTF-8"?>
    <ImportSession>
     <Documents>
      <Document DocumentClassName="RDOCCLASS" Processed="0" Priority="2">
       <DocumentFields>
        <DocumentField Name="DocumentID" Value="419" />
        <DocumentField Name="MessageID" Value="apap-12w-asqwe" />
        <DocumentField Name="AccountName" Value="John Thomas" />
        <DocumentField Name="AccountNumber" Value="1234567890" />
        <DocumentField Name="Contact" Value="00012736782345" />
       </DocumentFields>
      </Document>
     </Documents>
    </ImportSession>'

    EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData;

    INSERT INTO  dbo.[Account]([DocumentID], [MessageID], [AccountName], [AccountNumber], [Contact])
        SELECT * 
        FROM OPENXML(@idoc,/ImportSession/Documents/Document/DocumentFields',1)
        WITH (DocumentID varchar(20),MessageID varchar(30),
         AccountName varchar(20),AccountNumber varchar(20),
         Contact varchar(20)
         );

    EXEC sp_xml_removedocument @xmlData;
END

Thanks Arsh

No luck... details please. In what way does it not work? Getting an error? If so include the error message in your question. — TT.
– TT., Commented Feb 22, 2016 at 20:00
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 Feb 22, 2016 at 21:18

Gottfried Lesigang · Accepted Answer · 2016-02-22 20:17:40Z

3

OPENXML is absolutely outdated. This is the way you get the data from your XML variable in a modern way:

DECLARE @xmlData XML='<?xml version="1.0" encoding="UTF-8"?>
    <ImportSession>
     <Documents>
      <Document DocumentClassName="RDOCCLASS" Processed="0" Priority="2">
       <DocumentFields>
        <DocumentField Name="DocumentID" Value="419" />
        <DocumentField Name="MessageID" Value="apap-12w-asqwe" />
        <DocumentField Name="AccountName" Value="John Thomas" />
        <DocumentField Name="AccountNumber" Value="1234567890" />
        <DocumentField Name="Contact" Value="00012736782345" />
       </DocumentFields>
      </Document>
     </Documents>
    </ImportSession>';

SELECT Doc.value('(DocumentFields/DocumentField[@Name="DocumentID"]/@Value)[1]','int') AS DocumentID
      ,Doc.value('(DocumentFields/DocumentField[@Name="MessageID"]/@Value)[1]','varchar(max)') AS MessageID
      ,Doc.value('(DocumentFields/DocumentField[@Name="AccountName"]/@Value)[1]','varchar(max)') AS AccountName
      ,Doc.value('(DocumentFields/DocumentField[@Name="AccountNumber"]/@Value)[1]','varchar(max)') AS AccountNumber
      ,Doc.value('(DocumentFields/DocumentField[@Name="Contact"]/@Value)[1]','varchar(max)') AS Contact
FROM @xmlData.nodes('/ImportSession/Documents/Document') AS One(Doc)

So your SP should be this:

Create PROCEDURE [dbo].[SP_Test]
 (
    @xmlData As XML
 )

As
BEGIN



 INSERT INTO  dbo.[Account]([DocumentID],
                           [MessageID],
                           [AccountName],
                           [AccountNumber],
                           [Contact]
                          )

SELECT Doc.value('(DocumentFields/DocumentField[@Name="DocumentID"]/@Value)[1]','int') AS DocumentID
      ,Doc.value('(DocumentFields/DocumentField[@Name="MessageID"]/@Value)[1]','varchar(max)') AS MessageID
      ,Doc.value('(DocumentFields/DocumentField[@Name="AccountName"]/@Value)[1]','varchar(max)') AS AccountName
      ,Doc.value('(DocumentFields/DocumentField[@Name="AccountNumber"]/@Value)[1]','varchar(max)') AS AccountNumber
      ,Doc.value('(DocumentFields/DocumentField[@Name="Contact"]/@Value)[1]','varchar(max)') AS Contact
FROM @xmlData.nodes('/ImportSession/Documents/Document') AS One(Doc);
END

answered Feb 22, 2016 at 20:17

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

6 Comments

TT. Over a year ago

Ah, no pivoting needed the way you do it. =)

TT. Over a year ago

If I would mess around with my answer, I would probably turn up with yours =). I deleted it. I was trying to get a "document" number in there, but I don't want to mess around with a numbers table. Yours is more elegant. I still have much to learn ;-)

Gottfried Lesigang Over a year ago

@TT. You lost my upvote by deleting this. Poor you! I just placed a vote on another answer of yours to keep the damage away from you :-)

TT. Over a year ago

Oh I don't mind that, I'd rather be correct any day! =) Anyway I gained more insight from your answer, that's most important.

Gottfried Lesigang Over a year ago

@TT Oh I do mind that. I'm kind of adicted with collecting rep points. But you are right: In the months I'm contributing here I've learned incredibly much. Especially in such a case, were you place an answer and another answer is somehow better / more elegant / more performant one can learn best...

|

Collectives™ on Stack Overflow

Insert XML data into SQL Server

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related