OPEN XML help needed

Question

Post reply

OPEN XML help needed

George Thomas-241216

SSC Enthusiast

Points: 121
More actions
January 25, 2006 at 2:12 am

#170184

DECLARE @p_strXML VARCHAR(8000)
SET @p_strXML = '
<REQUEST>
     <NUMBER>1</NUMBER>
     <SUB_NUMBER>2</SUB_NUMBER>
     <DATA>
         <F1>1</F1>
         <DETAILS>
               <DETAIL>
                    <AMOUNT>1</AMOUNT>
                    <NOTE>1</NOTE>
              </DETAIL>
             <DETAIL>
                   <AMOUNT>2</AMOUNT>
                   <NOTE>2</NOTE>
             </DETAIL>
             <DETAIL>
                   <AMOUNT>3</AMOUNT>
                  <NOTE>3</NOTE>
             </DETAIL>
         </DETAILS>
     </DATA>
     <DATA>
         <F1>2</F1>
         <DETAILS>
               <DETAIL>
                    <AMOUNT>1</AMOUNT>
                    <NOTE>1</NOTE>
              </DETAIL>
               <DETAIL>
                   <AMOUNT>2</AMOUNT>
                   <NOTE>2</NOTE>
              </DETAIL>
         </DETAILS>
     </DATA>
</REQUEST>'
DECLARE @v_F1 VARCHAR(10)
DECLARE @v_AMOUNT DECIMAL
DECLARE @v_NOTE VARCHAR(120)
DECLARE @XMLDOCPOINTER INT
DECLARE @v_CURSOR CURSOR
DECLARE @v_CURSOR1 CURSOR
EXEC SP_XML_PREPAREDOCUMENT @XMLDOCPOINTER OUTPUT, @p_strXML
SET @v_CURSOR = CURSOR FOR
SELECT F1
FROM OPENXML (@XMLDOCPOINTER, '/REQUEST/DATA', 2)
WITH (
F1 VARCHAR(10) 'F1')
OPEN @v_CURSOR
FETCH NEXT FROM @v_CURSOR INTO @v_F1
WHILE (@@FETCH_STATUS = 0)
BEGIN
  --INSERT into the first Table
  PRINT 'INSERT into the first Table'
  SET @v_CURSOR1 = CURSOR FOR
  SELECT AMOUNT,NOTE
  FROM OPENXML (@XMLDOCPOINTER,'REQUEST/DATA/DETAILS/DETAIL',2)
  WITH (
   AMOUNT DECIMAL,
   NOTE VARCHAR(120))
  OPEN @v_CURSOR1
  FETCH NEXT FROM @v_CURSOR1 INTO @v_AMOUNT, @v_NOTE
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
   --Insert into Second Table
   PRINT 'Insert into Second Table '
   FETCH NEXT FROM @v_CURSOR1 INTO @v_AMOUNT,@v_NOTE
  END
FETCH NEXT FROM @v_CURSOR INTO @v_F1
END
The above is an XML parsing using SP_XML_PREPAREDOCUMENT and OPENXML.
With the above xml I am getting a result like
INSERT into the first Table
Insert into Second Table
Insert into Second Table
Insert into Second Table
Insert into Second Table
Insert into Second Table
Insert into Second Table
INSERT into the first Table
Insert into Second Table
Insert into Second Table
Insert into Second Table
Insert into Second Table
Insert into Second Table
Insert into Second Table
But I am expecting a result
INSERT into the first Table
Insert into Second Table
Insert into Second Table
Insert into Second Table
INSERT into the first Table
Insert into Second Table
Insert into Second Table
I tried many a things.But nothing is working out for me.Members please help.I am badly in need of it.
Also anybody can try the above code in Querry Analyser.
I am using SQL Server 2000
Thanks in advance
George

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

You messed up with cursors.

Why not to use simple and clear set approach?

DECLARE @p_strXML VARCHAR(8000)

SET @p_strXML = '

<SUB_NUMBER>2</SUB_NUMBER>

<DATA>

</DETAIL>

</DETAIL>

</DETAIL>

</DETAILS>

</DATA>

<DATA>

</DETAIL>

</DETAIL>

</DETAILS>

</DATA>

</REQUEST>'

DECLARE @XMLDOCPOINTER INT

DECLARE @FirstTable TABLE (ID VARCHAR(10))

DECLARE @SecondTable Table (F1Id VARCHAR(10), Amount DECIMAL(5,2), Note nvarchar(120))

--these may be your static tables, so you donn't need to declare it in such case

EXEC SP_XML_PREPAREDOCUMENT @XMLDOCPOINTER OUTPUT, @p_strXML

INSERT INTO @FirstTable (id)

SELECT F1

FROM OPENXML (@XMLDOCPOINTER, '/REQUEST/DATA', 2)

WITH (

F1 VARCHAR(10) 'F1')

INSERT INTO @SecondTable (F1Id, Amount, Note)

SELECT F1, AMOUNT, NOTE

FROM OPENXML (@XMLDOCPOINTER,'REQUEST/DATA/DETAILS/DETAIL',2)

WITH (

F1 VARCHAR(10) '../../F1',

AMOUNT DECIMAL 'AMOUNT',

NOTE VARCHAR(120) 'NOTE')

select * from @FirstTable

select * from @SecondTable

_____________
Code for TallyGenerator

George Thomas-241216 SSC Enthusiast Points: 121 More actions · Answer 2

Dear Sergiy

Thanks and it was a great help.I thaught of cursors because of the processes involved before Inserting to the first table and relatively it affects in the processes in the second table.Anyway I dont need it anymore I think and let me try this tested and proved way. I think it will serve my purpose.

Thank you once again for your kindness for taking ur valuble time to reply.

Sincerely

george