OPENXML sp_xml_preparedocument - how to use it?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • emsik1001
    New Member
    • Dec 2007
    • 93
    #1

    OPENXML sp_xml_preparedocument - how to use it?

    I'm trying to upload XML into SQL Server 2000. I've never used openxml and I'm struggling with it. Below is the procedure with data.

    Code:
    DECLARE @idoc int

DECLARE @doc varchar (1000)

SET @doc ='
<Root>
<MajorFamily>
        <FamilyName>CSSP Essential SW</FamilyName>
        <MinorFamily>
            <FamilyName>CSSP Ess SW for CallMgr Series Products</FamilyName>
             <ProductNumber>CON-SESW-SBCSEU</ProductNumber>
        </MinorFamily>
     </MajorFamily>
     <MajorFamily>
        <FamilyName>Catalyst 2900 Series</FamilyName>
        <MinorFamily>
            <FamilyName>Catalyst 2900 Series Accessories</FamilyName>
             <ProductNumber>CAB-RPSAC=</ProductNumber>
             <ProductNumber>CAB-RPSACE=</ProductNumber>
             <ProductNumber>CAB-RPSY-2218=</ProductNumber>
             <ProductNumber>CABLEGUARD-C2940=</ProductNumber>
             <ProductNumber>STK-RACKMOUNT-1RU=</ProductNumber>
             <ProductNumber>WS-X2948G-RACK=</ProductNumber>
             <ProductNumber>WS-X2980G-RACK=</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Catalyst 2900 Series Software</FamilyName>
             <ProductNumber>WS-C2980-EMS-LIC=</ProductNumber>
        </MinorFamily>
     </MajorFamily>
</Root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc


SELECT *
FROM OPENXML (@idoc, '???', 1)
WITH 

([MajorFamilyName] nvarchar(255) '???',
[MinorFamilyName] nvarchar(255) '???',
[ProductNunber] nvarchar(255) '???',
)

EXEC sp_xml_removedocument @idoc
    Tags: None
    • emsik1001
      New Member
      • Dec 2007
      • 93
      #2
      I've managed to run it on one example

      Code:
      DECLARE @idoc int

DECLARE @doc varchar (1000)

SET @doc ='
<Root>
    <MajorFamily>
        <FamilyName>Training</FamilyName>
        <MinorFamily>
            <FamilyName>Cisco Learning Credit</FamilyName>
             <ProductNumber>TRN-CLC-000</ProductNumber>
             <ProductNumber>TRN-CLC-001</ProductNumber>
             <ProductNumber>TRN-CLC-003</ProductNumber>
             <ProductNumber>TRN-CLC-004</ProductNumber>
        </MinorFamily>
     </MajorFamily>
</Root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
print @idoc

SELECT *
FROM OPENXML (@idoc, '/Root/MajorFamily/MinorFamily/ProductNumber', 1)
WITH 
([ImportDate] nvarchar(255) '../../FamilyName',
 [FileName] nvarchar(255) '../FamilyName',
 [test] nvarchar(255) '.'
)

EXEC sp_xml_removedocument @idoc
      But it fails if I add more data

      Code:
      DECLARE @idoc int

DECLARE @doc varchar (1000)

SET @doc ='
<Root>
     <MajorFamily>
        <FamilyName>Advanced Services</FamilyName>
        <MinorFamily>
            <FamilyName>Application Oriented Networking (AON)</FamilyName>
             <ProductNumber>AS-AON-PDI-8340-K9</ProductNumber>
             <ProductNumber>AS-AON-PDI-BUN-K9</ProductNumber>
             <ProductNumber>AS-AON-PDI-K9</ProductNumber>
             <ProductNumber>AS-AON-PDI-NM-K9</ProductNumber>
        </MinorFamily>
     </MajorFamily>
     <MajorFamily>
        <FamilyName>Advanced Technology</FamilyName>
        <MinorFamily>
            <FamilyName>Training Category A</FamilyName>
             <ProductNumber>AS-TRAIN-A</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category A for APAC</FamilyName>
             <ProductNumber>AS-TRAIN-A-A</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category A for LATAM</FamilyName>
             <ProductNumber>AS-TRAIN-A-L</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category B</FamilyName>
             <ProductNumber>AS-TRAIN-B</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category B for APAC</FamilyName>
             <ProductNumber>AS-TRAIN-B-A</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category B for LATAM</FamilyName>
             <ProductNumber>AS-TRAIN-B-L</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category C</FamilyName>
             <ProductNumber>AS-TRAIN-C</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category C for APAC</FamilyName>
             <ProductNumber>AS-TRAIN-C-A</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category C for LATAM</FamilyName>
             <ProductNumber>AS-TRAIN-C-L</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category D</FamilyName>
             <ProductNumber>AS-TRAIN-D</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category D for APAC</FamilyName>
             <ProductNumber>AS-TRAIN-D-A</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category D for LATAM</FamilyName>
             <ProductNumber>AS-TRAIN-D-L</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category E</FamilyName>
             <ProductNumber>AS-TRAIN-E</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category E for APAC</FamilyName>
             <ProductNumber>AS-TRAIN-E-A</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category E for LATAM</FamilyName>
             <ProductNumber>AS-TRAIN-E-L</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category F</FamilyName>
             <ProductNumber>AS-TRAIN-F</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category F for APAC</FamilyName>
             <ProductNumber>AS-TRAIN-F-A</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Training Category F for LATAM</FamilyName>
             <ProductNumber>AS-TRAIN-F-L</ProductNumber>
        </MinorFamily>
     </MajorFamily>
     <MajorFamily>
        <FamilyName>Application Control Engine (ACE)</FamilyName>
        <MinorFamily>
            <FamilyName>ACE Appliance</FamilyName>
             <ProductNumber>ACE-4710-1F-K9</ProductNumber>
             <ProductNumber>ACE-4710-2F-K9</ProductNumber>
             <ProductNumber>ACE-4710-BAS-SK-K9</ProductNumber>
             <ProductNumber>ACE-4710-BUN-SK-K9</ProductNumber>
             <ProductNumber>ACE-4710-K9</ProductNumber>
             <ProductNumber>ACE-4710-K9=</ProductNumber>
             <ProductNumber>ACE-AP-01-LIC</ProductNumber>
             <ProductNumber>ACE-AP-02-LIC</ProductNumber>
             <ProductNumber>ACE-AP-02-LIC=</ProductNumber>
             <ProductNumber>ACE-AP-04-UP1=</ProductNumber>
             <ProductNumber>ACE-AP-04-UP2=</ProductNumber>
             <ProductNumber>ACE-AP-C-1000-LIC</ProductNumber>
             <ProductNumber>ACE-AP-C-1000-LIC=</ProductNumber>
             <ProductNumber>ACE-AP-C-500-LIC</ProductNumber>
             <ProductNumber>ACE-AP-C-500-LIC=</ProductNumber>
             <ProductNumber>ACE-AP-C-UP1=</ProductNumber>
             <ProductNumber>ACE-AP-C-UP3=</ProductNumber>
             <ProductNumber>ACE-AP-OPT-LIC-K9</ProductNumber>
             <ProductNumber>ACE-AP-OPT-LIC-K9=</ProductNumber>
             <ProductNumber>ACE-AP-SSL-05K-K9</ProductNumber>
             <ProductNumber>ACE-AP-SSL-05K-K9=</ProductNumber>
             <ProductNumber>ACE-AP-SSL-7K-K9</ProductNumber>
             <ProductNumber>ACE-AP-SSL-7K-K9=</ProductNumber>
             <ProductNumber>ACE-AP-SSL-UP1-K9=</ProductNumber>
             <ProductNumber>ACE-AP-SW-1.7A</ProductNumber>
             <ProductNumber>ACE-AP-VIRT-020</ProductNumber>
             <ProductNumber>ACE-AP-VIRT-020=</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>ACE Global Site Selector</FamilyName>
             <ProductNumber>GSS-4492R-K9</ProductNumber>
             <ProductNumber>SF-GSS-DDOSLIC</ProductNumber>
             <ProductNumber>SF-GSS-DDOSLIC=</ProductNumber>
             <ProductNumber>SF-GSS-DNSLIC</ProductNumber>
             <ProductNumber>SF-GSS-DNSLIC=</ProductNumber>
             <ProductNumber>SF-GSS-V2.0-K9</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Application Control Engine (ACE) Bundles</FamilyName>
             <ProductNumber>WS-C6504-E-ACE-K9</ProductNumber>
             <ProductNumber>WS-C6504E-ACE20-K9</ProductNumber>
             <ProductNumber>WS-C6509-E-ACE-K9</ProductNumber>
             <ProductNumber>WS-C6509E-ACE20-K9</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Application Control Engine (ACE) Licenses</FamilyName>
             <ProductNumber>ACE-04G-LIC</ProductNumber>
             <ProductNumber>ACE-08G-LIC</ProductNumber>
             <ProductNumber>ACE-16G-LIC</ProductNumber>
             <ProductNumber>ACE-SBC-H248</ProductNumber>
             <ProductNumber>ACE-SBC-H323</ProductNumber>
             <ProductNumber>ACE-SBC-H323=</ProductNumber>
             <ProductNumber>ACE-SBC-NO-APP</ProductNumber>
             <ProductNumber>ACE-SBC-RTU</ProductNumber>
             <ProductNumber>ACE-SBC-SIP</ProductNumber>
             <ProductNumber>ACE-SBC-SIP=</ProductNumber>
             <ProductNumber>ACE-SBC-SW2000</ProductNumber>
             <ProductNumber>ACE-SBC-SW3000-K9</ProductNumber>
             <ProductNumber>ACE-SBC-SW3000-K9=</ProductNumber>
             <ProductNumber>ACE-SEC-LIC-K9</ProductNumber>
             <ProductNumber>ACE-SSL-05K-K9</ProductNumber>
             <ProductNumber>ACE-SSL-05K-K9=</ProductNumber>
             <ProductNumber>ACE-SSL-10K-K9</ProductNumber>
             <ProductNumber>ACE-SSL-10K-K9=</ProductNumber>
             <ProductNumber>ACE-SSL-15K-K9</ProductNumber>
             <ProductNumber>ACE-SSL-15K-K9=</ProductNumber>
             <ProductNumber>ACE-SSL-UP1-K9=</ProductNumber>
             <ProductNumber>ACE-SSL-UP2-K9=</ProductNumber>
             <ProductNumber>ACE-UPG1-LIC=</ProductNumber>
             <ProductNumber>ACE-UPG2-LIC=</ProductNumber>
             <ProductNumber>ACE-VIRT-020</ProductNumber>
             <ProductNumber>ACE-VIRT-020=</ProductNumber>
             <ProductNumber>ACE-VIRT-050</ProductNumber>
             <ProductNumber>ACE-VIRT-050=</ProductNumber>
             <ProductNumber>ACE-VIRT-100</ProductNumber>
             <ProductNumber>ACE-VIRT-100=</ProductNumber>
             <ProductNumber>ACE-VIRT-250</ProductNumber>
             <ProductNumber>ACE-VIRT-250=</ProductNumber>
             <ProductNumber>ACE-VIRT-UP1=</ProductNumber>
             <ProductNumber>ACE-VIRT-UP2=</ProductNumber>
             <ProductNumber>ACE-VIRT-UP3=</ProductNumber>
             <ProductNumber>ACE20-SBC-K9</ProductNumber>
             <ProductNumber>ACE20-SBC-K9=</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Application Control Engine (ACE) Service Module</FamilyName>
             <ProductNumber>ACE-SBC-SW2000-K9</ProductNumber>
             <ProductNumber>ACE-SBC-SW2000-K9=</ProductNumber>
             <ProductNumber>ACE10-6500-K9</ProductNumber>
             <ProductNumber>ACE10-6500-K9=</ProductNumber>
             <ProductNumber>ACE20-MOD-K9</ProductNumber>
             <ProductNumber>ACE20-MOD-K9=</ProductNumber>
             <ProductNumber>ACE20-SBC-K9</ProductNumber>
             <ProductNumber>ACE20-SBC-K9=</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Application Control Engine (ACE) Software</FamilyName>
             <ProductNumber>SC6K-3.0.0A14-ACE</ProductNumber>
             <ProductNumber>SC6K-3.0.0A16-ACE</ProductNumber>
             <ProductNumber>SC6K-A21-ACE</ProductNumber>
        </MinorFamily>
        <MinorFamily>
            <FamilyName>Application Control Engine (ACE) XML Gateway</FamilyName>
             <ProductNumber>ACE-WAF-GAT-LICFX</ProductNumber>
             <ProductNumber>ACE-WAF-GAT-LICFX=</ProductNumber>
             <ProductNumber>ACE-WAF-MGT-LICFX</ProductNumber>
             <ProductNumber>ACE-WAF-MGT-LICFX=</ProductNumber>
             <ProductNumber>ACE-XML-FIPS</ProductNumber>
             <ProductNumber>ACE-XML-GAT-LICFX</ProductNumber>
             <ProductNumber>ACE-XML-GAT-LICFX=</ProductNumber>
             <ProductNumber>ACE-XML-GATE-LIC</ProductNumber>
             <ProductNumber>ACE-XML-K9</ProductNumber>
             <ProductNumber>ACE-XML-MGMT-LIC</ProductNumber>
             <ProductNumber>ACE-XML-MGT-LICFX</ProductNumber>
             <ProductNumber>ACE-XML-MGT-LICFX=</ProductNumber>
             <ProductNumber>ACE-XML-NF-K9</ProductNumber>
             <ProductNumber>ACE-XML-NONFIPS</ProductNumber>
             <ProductNumber>ACE-XML-SW-5.0</ProductNumber>
             <ProductNumber>ACE-XML-SW-5.1</ProductNumber>
             <ProductNumber>ACE-XML-SW-5.2</ProductNumber>
             <ProductNumber>ACE-XML-SW-6.0</ProductNumber>
             <ProductNumber>HDD-AXG-360G5=</ProductNumber>
        </MinorFamily>
     </MajorFamily>
</Root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
print @idoc

SELECT *
FROM OPENXML (@idoc, '/Root/MajorFamily/MinorFamily/ProductNumber', 1)
WITH 
([ImportDate] nvarchar(255) '../../FamilyName',
 [FileName] nvarchar(255) '../FamilyName',
 [test] nvarchar(255) '.'
)

EXEC sp_xml_removedocument @idoc
      I'm getting this error

      Server: Msg 6603, Level 16, State 1, Procedure sp_xml_prepared ocument, Line 224
      XML parsing error: The following tags were not closed: Root, MajorFamily, MinorFamily, ProductNumber.

        Comment

        • emsik1001
          New Member
          • Dec 2007
          • 93
          #3
          It is working after changing data type to varchar(8000) and removing some characters.

          However the file I have is 8mb of text and I'm getting this error

          The text, ntext, and image data types are invalid for local variables.


          Does anyone know how to pass large file into stored procedure? Using T-SQL or VB?

            Comment

            • jgoemat
              New Member
              • Jan 2007
              • 1
              #4
              varchar(max)

              Try using varchar(max) instead of text
              http://www.teratrax.com/articles/varchar_max.html

                Comment

                • Yathirajalb
                  New Member
                  • Jan 2014
                  • 1
                  #5
                  Code:
                  BEGIN
DECLARE @idoc int 
  
DECLARE @doc nvarchar(max) 
  
SET @doc =' 
<Root> 
<MajorFamily> 
        <FamilyName>CSSP Essential SW</FamilyName> 
        <MinorFamily> 
            <FamilyName>CSSP Ess SW for CallMgr Series Products</FamilyName> 
             <ProductNumber>CON-SESW-SBCSEU</ProductNumber> 
        </MinorFamily> 
     </MajorFamily> 
     <MajorFamily> 
        <FamilyName>Catalyst 2900 Series</FamilyName> 
        <MinorFamily> 
            <FamilyName>Catalyst 2900 Series Accessories</FamilyName> 
             <ProductNumber>CAB-RPSAC=</ProductNumber> 
             <ProductNumber>CAB-RPSACE=</ProductNumber> 
             <ProductNumber>CAB-RPSY-2218=</ProductNumber> 
             <ProductNumber>CABLEGUARD-C2940=</ProductNumber> 
             <ProductNumber>STK-RACKMOUNT-1RU=</ProductNumber> 
             <ProductNumber>WS-X2948G-RACK=</ProductNumber> 
             <ProductNumber>WS-X2980G-RACK=</ProductNumber> 
        </MinorFamily> 
        <MinorFamily> 
            <FamilyName>Catalyst 2900 Series Software</FamilyName> 
             <ProductNumber>WS-C2980-EMS-LIC=</ProductNumber> 
        </MinorFamily> 
     </MajorFamily> 
</Root>' 
  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 
  
  
SELECT * 
FROM OPENXML (@idoc, 'Root/MajorFamily/MinorFamily/ProductNumber', 1) 
WITH ([MajorFamilyName] nvarchar(255) '../../FamilyName', 
[MinorFamilyName] nvarchar(255) '../FamilyName', 
[ProductNunber] nvarchar(255) '../ProductNumber') 
  
EXEC sp_xml_removedocument @idoc 
END
                  try with the above script .. it will work
                  Last edited by Niheel; Jan 2 '14, 01:10 PM.

                    Comment

                    • Dormilich
                      Recognized Expert Expert
                      • Aug 2008
                      • 8694
                      #6
                      @Yathirajalb, I guess after 4 years the problem is of no more importance.

                        Comment

                        Previous template Next
                        Working...