sql convert xml to varchar

Declare @ ID varchar (20), @ name varchar (20), @ XMLSet @ ID = 'all'Set @ name = 'select'Set @ xml = (select note. gp_code 'id', note. gp_name 'name', item. company_code 'id', item. company_name 'name', items. dept_code 'id', items. dept_name 'name' from GP note join company itemOn item. gp_code = Note. gp_code join (select * from Dept where dept_gp is not null)

Next, I will introduce a feature of using XML technology to merge rows and columns in SQL SERVER2005. DECLARE @ idoc intDECLARE @ doc xml SET @ doc =' ' EXEC sp_xml_preparedocument @ idoc OUTPUT, @ doc SELECT csif_cStateIDFrom openxml (@ idoc, '/condition/state ')WITH (csif_cStateID varchar (15 ))EXEC sp_xml_removedoc

Tags: case AAA Compact SOC ISS ble Article char = = SQL FOR XML also has a way of writing (with tag and union all, simple and easy to understand) Test environment: SQL, R2, 2010, 2012, 2014, etc. Declare @agent table (agentid int, Fname varchar (5), SSN varchar (one)) inser

Copy Code code as follows: --Create a test table DECLARE @Users TABLE ( ID INT IDENTITY (1,1), UserInfo XML ) ---insert test data DECLARE @xml XML SET @xml = ' INSERT into @Users (UserInfo) VALUES (@xml) --Insert a single node, (type: As First,as last,after (

, UserName, CityName)VALUES(1,'a','Shanghai' )INSERT into @T1(UserID, UserName, CityName)VALUES(2,'b','Beijing' )INSERT into @T1(UserID, UserName, CityName)VALUES(3,'C','Shanghai' )INSERT into @T1(UserID, UserName, CityName)VALUES(4,'D','Beijing' )INSERT into @T1(UserID, UserName, CityName)VALUES(5,'e','Shanghai' )SELECT * from @T1SELECTCityName, (SELECTUserName+ ',' from @T1 WHERECityName=A.cityname forXML PATH ("') ) asuserlist from @T1AGROUP byC

Sometimes it may be useful to associate or analyze data from SQL Server to other departments at once, a requirement that is certainly simple for SSIS, but a lot of times it makes a fuss to create a SSIS package just by exporting the data one at a time, while the SQL The Import Export tool for the server there are a lot of bugs, the easiest way is to bcp. Data exported to

Find all non-xml Indexes Find all non-xml Indexes The Code is as follows:DECLARE cur CURSORSELECT[Object_name] = s. name + '.' + OBJECT_NAME (A. object_id ),B. nameFROM sys. dm_db_index_physical_stats (DB_ID ('adventureworks'), NULL, null, null) ASJOIN sys. indexes AS BOn a. [object_id] = B. [object_id]And a. [index_id] = B. [index_id]JOIN sys. objects AS oOn a. [object_id] = o. [object_id]JOIN sys. sche

Sometimes the need to export data from SQL Server to other departments may be correlated or analyzed, which is of course very simple for SSIS, but many times it takes a lot of time to simply export the data and create an SSIS package that makes a fuss, while the SQL The Import and Export tool for the server where the bugs are still pretty much, the simplest way is bcp. Data export to XMLAfter

* from XMLTABLE (' $B/deal_basic/user_deal_info ' passing XMLTYPE ('COLUMNS user_deal_a VARCHAR2 () PATH '/user_deal_info/user_deal_id[@name = "AAA"],User_deal_b VARCHAR2 () PATH '/user_deal_info/user_deal_id[@name = "BBB"] ',Deal_inure_time VARCHAR2 () PATH '/user_deal_info/deal_inure_time ',Deal_expire_time VARCHAR2 () PATH '/user_deal_info/deal_expire_time ',Deal_create_time VARCHAR2 () PATH '/user_deal_info/deal_create_time ')It is a necessity to parse