469,326 Members | 1,320 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

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.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @idoc int
  2.  
  3. DECLARE @doc varchar (1000)
  4.  
  5. SET @doc ='
  6. <Root>
  7. <MajorFamily>
  8.         <FamilyName>CSSP Essential SW</FamilyName>
  9.         <MinorFamily>
  10.             <FamilyName>CSSP Ess SW for CallMgr Series Products</FamilyName>
  11.              <ProductNumber>CON-SESW-SBCSEU</ProductNumber>
  12.         </MinorFamily>
  13.      </MajorFamily>
  14.      <MajorFamily>
  15.         <FamilyName>Catalyst 2900 Series</FamilyName>
  16.         <MinorFamily>
  17.             <FamilyName>Catalyst 2900 Series Accessories</FamilyName>
  18.              <ProductNumber>CAB-RPSAC=</ProductNumber>
  19.              <ProductNumber>CAB-RPSACE=</ProductNumber>
  20.              <ProductNumber>CAB-RPSY-2218=</ProductNumber>
  21.              <ProductNumber>CABLEGUARD-C2940=</ProductNumber>
  22.              <ProductNumber>STK-RACKMOUNT-1RU=</ProductNumber>
  23.              <ProductNumber>WS-X2948G-RACK=</ProductNumber>
  24.              <ProductNumber>WS-X2980G-RACK=</ProductNumber>
  25.         </MinorFamily>
  26.         <MinorFamily>
  27.             <FamilyName>Catalyst 2900 Series Software</FamilyName>
  28.              <ProductNumber>WS-C2980-EMS-LIC=</ProductNumber>
  29.         </MinorFamily>
  30.      </MajorFamily>
  31. </Root>'
  32.  
  33. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
  34.  
  35.  
  36. SELECT *
  37. FROM OPENXML (@idoc, '???', 1)
  38. WITH 
  39.  
  40. ([MajorFamilyName] nvarchar(255) '???',
  41. [MinorFamilyName] nvarchar(255) '???',
  42. [ProductNunber] nvarchar(255) '???',
  43. )
  44.  
  45. EXEC sp_xml_removedocument @idoc
  46.  
Jun 23 '08 #1
5 20294
I've managed to run it on one example

Expand|Select|Wrap|Line Numbers
  1. DECLARE @idoc int
  2.  
  3. DECLARE @doc varchar (1000)
  4.  
  5. SET @doc ='
  6. <Root>
  7.     <MajorFamily>
  8.         <FamilyName>Training</FamilyName>
  9.         <MinorFamily>
  10.             <FamilyName>Cisco Learning Credit</FamilyName>
  11.              <ProductNumber>TRN-CLC-000</ProductNumber>
  12.              <ProductNumber>TRN-CLC-001</ProductNumber>
  13.              <ProductNumber>TRN-CLC-003</ProductNumber>
  14.              <ProductNumber>TRN-CLC-004</ProductNumber>
  15.         </MinorFamily>
  16.      </MajorFamily>
  17. </Root>'
  18.  
  19. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
  20. print @idoc
  21.  
  22. SELECT *
  23. FROM OPENXML (@idoc, '/Root/MajorFamily/MinorFamily/ProductNumber', 1)
  24. WITH 
  25. ([ImportDate] nvarchar(255) '../../FamilyName',
  26.  [FileName] nvarchar(255) '../FamilyName',
  27.  [test] nvarchar(255) '.'
  28. )
  29.  
  30. EXEC sp_xml_removedocument @idoc
  31.  
But it fails if I add more data

Expand|Select|Wrap|Line Numbers
  1. DECLARE @idoc int
  2.  
  3. DECLARE @doc varchar (1000)
  4.  
  5. SET @doc ='
  6. <Root>
  7.      <MajorFamily>
  8.         <FamilyName>Advanced Services</FamilyName>
  9.         <MinorFamily>
  10.             <FamilyName>Application Oriented Networking (AON)</FamilyName>
  11.              <ProductNumber>AS-AON-PDI-8340-K9</ProductNumber>
  12.              <ProductNumber>AS-AON-PDI-BUN-K9</ProductNumber>
  13.              <ProductNumber>AS-AON-PDI-K9</ProductNumber>
  14.              <ProductNumber>AS-AON-PDI-NM-K9</ProductNumber>
  15.         </MinorFamily>
  16.      </MajorFamily>
  17.      <MajorFamily>
  18.         <FamilyName>Advanced Technology</FamilyName>
  19.         <MinorFamily>
  20.             <FamilyName>Training Category A</FamilyName>
  21.              <ProductNumber>AS-TRAIN-A</ProductNumber>
  22.         </MinorFamily>
  23.         <MinorFamily>
  24.             <FamilyName>Training Category A for APAC</FamilyName>
  25.              <ProductNumber>AS-TRAIN-A-A</ProductNumber>
  26.         </MinorFamily>
  27.         <MinorFamily>
  28.             <FamilyName>Training Category A for LATAM</FamilyName>
  29.              <ProductNumber>AS-TRAIN-A-L</ProductNumber>
  30.         </MinorFamily>
  31.         <MinorFamily>
  32.             <FamilyName>Training Category B</FamilyName>
  33.              <ProductNumber>AS-TRAIN-B</ProductNumber>
  34.         </MinorFamily>
  35.         <MinorFamily>
  36.             <FamilyName>Training Category B for APAC</FamilyName>
  37.              <ProductNumber>AS-TRAIN-B-A</ProductNumber>
  38.         </MinorFamily>
  39.         <MinorFamily>
  40.             <FamilyName>Training Category B for LATAM</FamilyName>
  41.              <ProductNumber>AS-TRAIN-B-L</ProductNumber>
  42.         </MinorFamily>
  43.         <MinorFamily>
  44.             <FamilyName>Training Category C</FamilyName>
  45.              <ProductNumber>AS-TRAIN-C</ProductNumber>
  46.         </MinorFamily>
  47.         <MinorFamily>
  48.             <FamilyName>Training Category C for APAC</FamilyName>
  49.              <ProductNumber>AS-TRAIN-C-A</ProductNumber>
  50.         </MinorFamily>
  51.         <MinorFamily>
  52.             <FamilyName>Training Category C for LATAM</FamilyName>
  53.              <ProductNumber>AS-TRAIN-C-L</ProductNumber>
  54.         </MinorFamily>
  55.         <MinorFamily>
  56.             <FamilyName>Training Category D</FamilyName>
  57.              <ProductNumber>AS-TRAIN-D</ProductNumber>
  58.         </MinorFamily>
  59.         <MinorFamily>
  60.             <FamilyName>Training Category D for APAC</FamilyName>
  61.              <ProductNumber>AS-TRAIN-D-A</ProductNumber>
  62.         </MinorFamily>
  63.         <MinorFamily>
  64.             <FamilyName>Training Category D for LATAM</FamilyName>
  65.              <ProductNumber>AS-TRAIN-D-L</ProductNumber>
  66.         </MinorFamily>
  67.         <MinorFamily>
  68.             <FamilyName>Training Category E</FamilyName>
  69.              <ProductNumber>AS-TRAIN-E</ProductNumber>
  70.         </MinorFamily>
  71.         <MinorFamily>
  72.             <FamilyName>Training Category E for APAC</FamilyName>
  73.              <ProductNumber>AS-TRAIN-E-A</ProductNumber>
  74.         </MinorFamily>
  75.         <MinorFamily>
  76.             <FamilyName>Training Category E for LATAM</FamilyName>
  77.              <ProductNumber>AS-TRAIN-E-L</ProductNumber>
  78.         </MinorFamily>
  79.         <MinorFamily>
  80.             <FamilyName>Training Category F</FamilyName>
  81.              <ProductNumber>AS-TRAIN-F</ProductNumber>
  82.         </MinorFamily>
  83.         <MinorFamily>
  84.             <FamilyName>Training Category F for APAC</FamilyName>
  85.              <ProductNumber>AS-TRAIN-F-A</ProductNumber>
  86.         </MinorFamily>
  87.         <MinorFamily>
  88.             <FamilyName>Training Category F for LATAM</FamilyName>
  89.              <ProductNumber>AS-TRAIN-F-L</ProductNumber>
  90.         </MinorFamily>
  91.      </MajorFamily>
  92.      <MajorFamily>
  93.         <FamilyName>Application Control Engine (ACE)</FamilyName>
  94.         <MinorFamily>
  95.             <FamilyName>ACE Appliance</FamilyName>
  96.              <ProductNumber>ACE-4710-1F-K9</ProductNumber>
  97.              <ProductNumber>ACE-4710-2F-K9</ProductNumber>
  98.              <ProductNumber>ACE-4710-BAS-SK-K9</ProductNumber>
  99.              <ProductNumber>ACE-4710-BUN-SK-K9</ProductNumber>
  100.              <ProductNumber>ACE-4710-K9</ProductNumber>
  101.              <ProductNumber>ACE-4710-K9=</ProductNumber>
  102.              <ProductNumber>ACE-AP-01-LIC</ProductNumber>
  103.              <ProductNumber>ACE-AP-02-LIC</ProductNumber>
  104.              <ProductNumber>ACE-AP-02-LIC=</ProductNumber>
  105.              <ProductNumber>ACE-AP-04-UP1=</ProductNumber>
  106.              <ProductNumber>ACE-AP-04-UP2=</ProductNumber>
  107.              <ProductNumber>ACE-AP-C-1000-LIC</ProductNumber>
  108.              <ProductNumber>ACE-AP-C-1000-LIC=</ProductNumber>
  109.              <ProductNumber>ACE-AP-C-500-LIC</ProductNumber>
  110.              <ProductNumber>ACE-AP-C-500-LIC=</ProductNumber>
  111.              <ProductNumber>ACE-AP-C-UP1=</ProductNumber>
  112.              <ProductNumber>ACE-AP-C-UP3=</ProductNumber>
  113.              <ProductNumber>ACE-AP-OPT-LIC-K9</ProductNumber>
  114.              <ProductNumber>ACE-AP-OPT-LIC-K9=</ProductNumber>
  115.              <ProductNumber>ACE-AP-SSL-05K-K9</ProductNumber>
  116.              <ProductNumber>ACE-AP-SSL-05K-K9=</ProductNumber>
  117.              <ProductNumber>ACE-AP-SSL-7K-K9</ProductNumber>
  118.              <ProductNumber>ACE-AP-SSL-7K-K9=</ProductNumber>
  119.              <ProductNumber>ACE-AP-SSL-UP1-K9=</ProductNumber>
  120.              <ProductNumber>ACE-AP-SW-1.7A</ProductNumber>
  121.              <ProductNumber>ACE-AP-VIRT-020</ProductNumber>
  122.              <ProductNumber>ACE-AP-VIRT-020=</ProductNumber>
  123.         </MinorFamily>
  124.         <MinorFamily>
  125.             <FamilyName>ACE Global Site Selector</FamilyName>
  126.              <ProductNumber>GSS-4492R-K9</ProductNumber>
  127.              <ProductNumber>SF-GSS-DDOSLIC</ProductNumber>
  128.              <ProductNumber>SF-GSS-DDOSLIC=</ProductNumber>
  129.              <ProductNumber>SF-GSS-DNSLIC</ProductNumber>
  130.              <ProductNumber>SF-GSS-DNSLIC=</ProductNumber>
  131.              <ProductNumber>SF-GSS-V2.0-K9</ProductNumber>
  132.         </MinorFamily>
  133.         <MinorFamily>
  134.             <FamilyName>Application Control Engine (ACE) Bundles</FamilyName>
  135.              <ProductNumber>WS-C6504-E-ACE-K9</ProductNumber>
  136.              <ProductNumber>WS-C6504E-ACE20-K9</ProductNumber>
  137.              <ProductNumber>WS-C6509-E-ACE-K9</ProductNumber>
  138.              <ProductNumber>WS-C6509E-ACE20-K9</ProductNumber>
  139.         </MinorFamily>
  140.         <MinorFamily>
  141.             <FamilyName>Application Control Engine (ACE) Licenses</FamilyName>
  142.              <ProductNumber>ACE-04G-LIC</ProductNumber>
  143.              <ProductNumber>ACE-08G-LIC</ProductNumber>
  144.              <ProductNumber>ACE-16G-LIC</ProductNumber>
  145.              <ProductNumber>ACE-SBC-H248</ProductNumber>
  146.              <ProductNumber>ACE-SBC-H323</ProductNumber>
  147.              <ProductNumber>ACE-SBC-H323=</ProductNumber>
  148.              <ProductNumber>ACE-SBC-NO-APP</ProductNumber>
  149.              <ProductNumber>ACE-SBC-RTU</ProductNumber>
  150.              <ProductNumber>ACE-SBC-SIP</ProductNumber>
  151.              <ProductNumber>ACE-SBC-SIP=</ProductNumber>
  152.              <ProductNumber>ACE-SBC-SW2000</ProductNumber>
  153.              <ProductNumber>ACE-SBC-SW3000-K9</ProductNumber>
  154.              <ProductNumber>ACE-SBC-SW3000-K9=</ProductNumber>
  155.              <ProductNumber>ACE-SEC-LIC-K9</ProductNumber>
  156.              <ProductNumber>ACE-SSL-05K-K9</ProductNumber>
  157.              <ProductNumber>ACE-SSL-05K-K9=</ProductNumber>
  158.              <ProductNumber>ACE-SSL-10K-K9</ProductNumber>
  159.              <ProductNumber>ACE-SSL-10K-K9=</ProductNumber>
  160.              <ProductNumber>ACE-SSL-15K-K9</ProductNumber>
  161.              <ProductNumber>ACE-SSL-15K-K9=</ProductNumber>
  162.              <ProductNumber>ACE-SSL-UP1-K9=</ProductNumber>
  163.              <ProductNumber>ACE-SSL-UP2-K9=</ProductNumber>
  164.              <ProductNumber>ACE-UPG1-LIC=</ProductNumber>
  165.              <ProductNumber>ACE-UPG2-LIC=</ProductNumber>
  166.              <ProductNumber>ACE-VIRT-020</ProductNumber>
  167.              <ProductNumber>ACE-VIRT-020=</ProductNumber>
  168.              <ProductNumber>ACE-VIRT-050</ProductNumber>
  169.              <ProductNumber>ACE-VIRT-050=</ProductNumber>
  170.              <ProductNumber>ACE-VIRT-100</ProductNumber>
  171.              <ProductNumber>ACE-VIRT-100=</ProductNumber>
  172.              <ProductNumber>ACE-VIRT-250</ProductNumber>
  173.              <ProductNumber>ACE-VIRT-250=</ProductNumber>
  174.              <ProductNumber>ACE-VIRT-UP1=</ProductNumber>
  175.              <ProductNumber>ACE-VIRT-UP2=</ProductNumber>
  176.              <ProductNumber>ACE-VIRT-UP3=</ProductNumber>
  177.              <ProductNumber>ACE20-SBC-K9</ProductNumber>
  178.              <ProductNumber>ACE20-SBC-K9=</ProductNumber>
  179.         </MinorFamily>
  180.         <MinorFamily>
  181.             <FamilyName>Application Control Engine (ACE) Service Module</FamilyName>
  182.              <ProductNumber>ACE-SBC-SW2000-K9</ProductNumber>
  183.              <ProductNumber>ACE-SBC-SW2000-K9=</ProductNumber>
  184.              <ProductNumber>ACE10-6500-K9</ProductNumber>
  185.              <ProductNumber>ACE10-6500-K9=</ProductNumber>
  186.              <ProductNumber>ACE20-MOD-K9</ProductNumber>
  187.              <ProductNumber>ACE20-MOD-K9=</ProductNumber>
  188.              <ProductNumber>ACE20-SBC-K9</ProductNumber>
  189.              <ProductNumber>ACE20-SBC-K9=</ProductNumber>
  190.         </MinorFamily>
  191.         <MinorFamily>
  192.             <FamilyName>Application Control Engine (ACE) Software</FamilyName>
  193.              <ProductNumber>SC6K-3.0.0A14-ACE</ProductNumber>
  194.              <ProductNumber>SC6K-3.0.0A16-ACE</ProductNumber>
  195.              <ProductNumber>SC6K-A21-ACE</ProductNumber>
  196.         </MinorFamily>
  197.         <MinorFamily>
  198.             <FamilyName>Application Control Engine (ACE) XML Gateway</FamilyName>
  199.              <ProductNumber>ACE-WAF-GAT-LICFX</ProductNumber>
  200.              <ProductNumber>ACE-WAF-GAT-LICFX=</ProductNumber>
  201.              <ProductNumber>ACE-WAF-MGT-LICFX</ProductNumber>
  202.              <ProductNumber>ACE-WAF-MGT-LICFX=</ProductNumber>
  203.              <ProductNumber>ACE-XML-FIPS</ProductNumber>
  204.              <ProductNumber>ACE-XML-GAT-LICFX</ProductNumber>
  205.              <ProductNumber>ACE-XML-GAT-LICFX=</ProductNumber>
  206.              <ProductNumber>ACE-XML-GATE-LIC</ProductNumber>
  207.              <ProductNumber>ACE-XML-K9</ProductNumber>
  208.              <ProductNumber>ACE-XML-MGMT-LIC</ProductNumber>
  209.              <ProductNumber>ACE-XML-MGT-LICFX</ProductNumber>
  210.              <ProductNumber>ACE-XML-MGT-LICFX=</ProductNumber>
  211.              <ProductNumber>ACE-XML-NF-K9</ProductNumber>
  212.              <ProductNumber>ACE-XML-NONFIPS</ProductNumber>
  213.              <ProductNumber>ACE-XML-SW-5.0</ProductNumber>
  214.              <ProductNumber>ACE-XML-SW-5.1</ProductNumber>
  215.              <ProductNumber>ACE-XML-SW-5.2</ProductNumber>
  216.              <ProductNumber>ACE-XML-SW-6.0</ProductNumber>
  217.              <ProductNumber>HDD-AXG-360G5=</ProductNumber>
  218.         </MinorFamily>
  219.      </MajorFamily>
  220. </Root>'
  221.  
  222. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
  223. print @idoc
  224.  
  225. SELECT *
  226. FROM OPENXML (@idoc, '/Root/MajorFamily/MinorFamily/ProductNumber', 1)
  227. WITH 
  228. ([ImportDate] nvarchar(255) '../../FamilyName',
  229.  [FileName] nvarchar(255) '../FamilyName',
  230.  [test] nvarchar(255) '.'
  231. )
  232.  
  233. EXEC sp_xml_removedocument @idoc
  234.  
I'm getting this error

Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 224
XML parsing error: The following tags were not closed: Root, MajorFamily, MinorFamily, ProductNumber.
Jun 23 '08 #2
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?
Jun 24 '08 #3
Try using varchar(max) instead of text
http://www.teratrax.com/articles/varchar_max.html
Jan 25 '10 #4
Expand|Select|Wrap|Line Numbers
  1. BEGIN
  2. DECLARE @idoc int 
  3.  
  4. DECLARE @doc nvarchar(max) 
  5.  
  6. SET @doc =' 
  7. <Root> 
  8. <MajorFamily> 
  9.         <FamilyName>CSSP Essential SW</FamilyName> 
  10.         <MinorFamily> 
  11.             <FamilyName>CSSP Ess SW for CallMgr Series Products</FamilyName> 
  12.              <ProductNumber>CON-SESW-SBCSEU</ProductNumber> 
  13.         </MinorFamily> 
  14.      </MajorFamily> 
  15.      <MajorFamily> 
  16.         <FamilyName>Catalyst 2900 Series</FamilyName> 
  17.         <MinorFamily> 
  18.             <FamilyName>Catalyst 2900 Series Accessories</FamilyName> 
  19.              <ProductNumber>CAB-RPSAC=</ProductNumber> 
  20.              <ProductNumber>CAB-RPSACE=</ProductNumber> 
  21.              <ProductNumber>CAB-RPSY-2218=</ProductNumber> 
  22.              <ProductNumber>CABLEGUARD-C2940=</ProductNumber> 
  23.              <ProductNumber>STK-RACKMOUNT-1RU=</ProductNumber> 
  24.              <ProductNumber>WS-X2948G-RACK=</ProductNumber> 
  25.              <ProductNumber>WS-X2980G-RACK=</ProductNumber> 
  26.         </MinorFamily> 
  27.         <MinorFamily> 
  28.             <FamilyName>Catalyst 2900 Series Software</FamilyName> 
  29.              <ProductNumber>WS-C2980-EMS-LIC=</ProductNumber> 
  30.         </MinorFamily> 
  31.      </MajorFamily> 
  32. </Root>' 
  33.  
  34. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 
  35.  
  36.  
  37. SELECT * 
  38. FROM OPENXML (@idoc, 'Root/MajorFamily/MinorFamily/ProductNumber', 1) 
  39. WITH ([MajorFamilyName] nvarchar(255) '../../FamilyName', 
  40. [MinorFamilyName] nvarchar(255) '../FamilyName', 
  41. [ProductNunber] nvarchar(255) '../ProductNumber') 
  42.  
  43. EXEC sp_xml_removedocument @idoc 
  44. END
try with the above script .. it will work
Jan 1 '14 #5
Dormilich
8,651 Expert Mod 8TB
@Yathirajalb, I guess after 4 years the problem is of no more importance.
Jan 2 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by JM | last post: by
1 post views Thread by jrd | last post: by
2 posts views Thread by Bostonasian | last post: by
1 post views Thread by Sandy | last post: by
reply views Thread by Micke | last post: by
1 post views Thread by figital | last post: by
2 posts views Thread by Shilpa | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.