{"id":1376,"date":"2010-12-04T18:38:20","date_gmt":"2010-12-04T13:08:20","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=1376"},"modified":"2010-12-04T18:38:20","modified_gmt":"2010-12-04T13:08:20","slug":"use-of-openxml-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/use-of-openxml-in-sql-server\/","title":{"rendered":"Use of OPENXML in SQL Server"},"content":{"rendered":"

In SQL Server, OpenXML <\/strong>is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string. OpenXML is used to parse the XML in Rowset data form.<\/p>\n

In this tutorial, i have created one XML string and saved the parsed data in Table variable. If XML data is present in table, then any operation can be performed.<\/p>\n

<\/p>\n

Source code:<\/span><\/strong><\/p>\n

\nDECLARE @friendsXML Varchar(600)\n\nSET @friendsXML =\n'\n<Friends>\n<Friend>\n<FName>Santosh<\/FName>\n<LName>Karemore<\/LName>\n<Technology>SAP<\/Technology>\n<Location>Pune<\/Location>\n<\/Friend>\n<Friend>\n<FName>Manoranjan<\/FName>\n<LName>Sahoo<\/LName>\n<Technology>IT Consultant<\/Technology>\n<Location>Tokyo<\/Location>\n<\/Friend>\n<Friend>\n<FName>Subodh<\/FName>\n<LName>Singh<\/LName>\n<Technology>JIRA<\/Technology>\n<Location>Jabalpur<\/Location>\n<\/Friend>\n<Friend>\n<FName>Praveen<\/FName>\n<LName>Meghwal<\/LName>\n<Technology>.NET<\/Technology>\n<Location>Ujjain<\/Location>\n<\/Friend>\n<\/Friends>\n'\n\nDECLARE @friends TABLE (\n  Id  INT IDENTITY(1,1),\n  FName VARCHAR(30),\n  LName VARCHAR(30),\n  Technology VARCHAR(30),\n  Location VARCHAR(30)\n\t\t       )\n\nDECLARE @XMLDocPointer INT\n\n  --Preprare the internal XML\n  EXEC sp_xml_preparedocument @XMLDOcPointer OUTPUT,\n @friendsXML\n\n  --Insert the record in @friends table\n  INSERT INTO @friends (\n               FName,LName,Technology,Location)\n  SELECT\n        *\n  FROM OPENXML(@XMLDocPointer, '\/Friends\/Friend',2)\n    WITH\n    (\n              FName VARCHAR(30),\n\t\t\t  LName VARCHAR(30),\n\t\t\t  Technology VARCHAR(30),\n\t\t\t  Location VARCHAR(30)\n    )\n\n  -- Remove the pointer from memory\n  EXEC sp_xml_removedocument @XMLDocPointer\n\n\tSELECT\n\tId ,FName,LName,Technology,Location\n\tFROM @friends\n<\/pre>\n
Output:<\/p>\n
\"OpenXML<\/a>
OpenXML in SQL Server<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"
In SQL Server, OpenXML is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[14],"tags":[292],"class_list":["post-1376","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":2861,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/pagination-and-switch-case-in-where-clause-sql-server\/","url_meta":{"origin":1376,"position":0},"title":"Pagination and Switch Case in Where clause – SQL Server","author":"Jitendra","date":"May 19, 2012","format":false,"excerpt":"Example of using pagination and switch case in where clause in SQL server","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"SQL Server Employee Table","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2012\/05\/SQL-Server-Employee-Table.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":191,"url":"https:\/\/www.jitendrazaa.com\/blog\/microsoft\/net\/datagrid-example-part-1\/","url_meta":{"origin":1376,"position":1},"title":"DataGrid Example – Part 1","author":"Jitendra","date":"May 18, 2010","format":false,"excerpt":"For the basics of DataGrid please refer this post: https:\/\/jitendrazaa.com\/blog\/?p=188 In this example, i will explain the basics of DataGrid control like Theming, Databinding etc. We will start our example with creating the SQL Express database of Employee which will contain the following fields : Id, FName, LName, Email. Now\u2026","rel":"","context":"In "ASP.NET"","block_context":{"text":"ASP.NET","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/microsoft\/net\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/05\/Employee.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":936,"url":"https:\/\/www.jitendrazaa.com\/blog\/java\/jdbc-example-with-microsoft-access-in-swing\/","url_meta":{"origin":1376,"position":2},"title":"JDBC Example with Microsoft Access in Swing, Next and Previous navigation","author":"Jitendra","date":"August 31, 2010","format":false,"excerpt":"JDBC - Java Database Connectivity example in Swing","rel":"","context":"In "JAVA"","block_context":{"text":"JAVA","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/java\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2347,"url":"https:\/\/www.jitendrazaa.com\/blog\/java\/hibernate\/step-by-step-hibernate-tutorial-using-eclipse-wtp\/","url_meta":{"origin":1376,"position":3},"title":"Step By Step Hibernate Tutorial Using eclipse WTP","author":"Jitendra","date":"August 8, 2011","format":false,"excerpt":"Step By Step Hibernate (ORM Tool) Tutorial Using eclipse WTP","rel":"","context":"In "Hibernate"","block_context":{"text":"Hibernate","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/java\/hibernate\/"},"img":{"alt_text":"Eclipse Install New Software - Hibernate","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2011\/08\/Eclipse-Install-New-Software-Hibernate.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2281,"url":"https:\/\/www.jitendrazaa.com\/blog\/webtech\/web\/disable-inputs-after-submit-to-avoid-double-submission-using-jquery-and-ajax\/","url_meta":{"origin":1376,"position":4},"title":"Disable inputs after submit to avoid double submission using JQuery and Ajax","author":"Jitendra","date":"June 24, 2011","format":false,"excerpt":"This article will explain the safe way to submit the forms using JQuery. It will avoid accidental double submission.","rel":"","context":"In "HTML"","block_context":{"text":"HTML","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/webtech\/web\/"},"img":{"alt_text":"Disable inputs after submit to avoid double submission using JQuery","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2011\/06\/Disable-inputs-after-submit-to-avoid-double-submission-using-JQuery.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":5882,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-read-all-files-in-directory-and-store-in-table\/","url_meta":{"origin":1376,"position":5},"title":"SQL Server – Read all files in directory and store in Table","author":"Jitendra","date":"January 18, 2017","format":false,"excerpt":"T-SQL Script to read all files in directory and store back in table - SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1376","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=1376"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1376\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=1376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=1376"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=1376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}