February 19, 2014 at 4:25 am
Morning All,
I have a table called tblInterface.
Within this table is a field called vchConfig, which is a data type of varchar(4000).
Within this field as an example is a bunch of XML.
<?xml version="1.0" encoding="utf-16"?> <InterfaceConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Source>.</Source> <Archive>.</Archive> <Log>.</Log> <Filter>PU*.csv</Filter> <Delimiter>"|"</Delimiter> <CreateAssets>true</CreateAssets> <UpdateAddresses>true</UpdateAddresses> <ManagementGroupId>1</ManagementGroupId> <ClearTenantRefusals>false</ClearTenantRefusals> <ClearTenantCharIds> <int>50</int> <int>51</int> <int>52</int> <int>53</int> <int>54</int> <int>55</int> <int>56</int> <int>57</int> <int>58</int> <int>59</int> <int>60</int> <int>61</int> <int>62</int> <int>63</int> <int>64</int> <int>65</int> <int>66</int> <int>67</int> <int>68</int> <int>69</int> <int>70</int> <int>74</int> <int>81</int> <int>85</int> <int>72</int> <int>73</int> <int>75</int> <int>76</int> <int>77</int> <int>78</int> <int>79</int> <int>80</int> <int>82</int> <int>83</int> <int>84</int> </ClearTenantCharIds> </InterfaceConfig>
As part of a nightly restore of a train database with live data, I also want to amend this field and in particular the contents of the Source, Archive and Log tags. How would I go about doing that in TSQL, as I have never come across XML within SQL before.
Any pointers would be great.
Thanks
February 19, 2014 at 5:52 am
declare @xml as xml
select @xml = CAST(N'<?xml version="1.0" encoding="utf-16"?> <InterfaceConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Source>.</Source> <Archive>.</Archive> <Log>.</Log> <Filter>PU*.csv</Filter> <Delimiter>"|"</Delimiter> <CreateAssets>true</CreateAssets> <UpdateAddresses>true</UpdateAddresses> <ManagementGroupId>1</ManagementGroupId> <ClearTenantRefusals>false</ClearTenantRefusals> <ClearTenantCharIds> <int>50</int> <int>51</int> <int>52</int> <int>53</int> <int>54</int> <int>55</int> <int>56</int> <int>57</int> <int>58</int> <int>59</int> <int>60</int> <int>61</int> <int>62</int> <int>63</int> <int>64</int> <int>65</int> <int>66</int> <int>67</int> <int>68</int> <int>69</int> <int>70</int> <int>74</int> <int>81</int> <int>85</int> <int>72</int> <int>73</int> <int>75</int> <int>76</int> <int>77</int> <int>78</int> <int>79</int> <int>80</int> <int>82</int> <int>83</int> <int>84</int> </ClearTenantCharIds> </InterfaceConfig>' as xml)
set @xml.modify('replace value of (/InterfaceConfig/Source/text())[1]with ("this is a source")');
set @xml.modify('replace value of (/InterfaceConfig/Archive/text())[1]with ("this is a archive")');
set @xml.modify('replace value of (/InterfaceConfig/Log/text())[1]with ("this is a Log")');
select @xml
Hope it helps
February 19, 2014 at 6:10 am
Thanks for that - it doesn't actually update the vchConfig field though?
February 19, 2014 at 6:14 am
Ryan Keast (2/19/2014)
Thanks for that - it doesn't actually update the vchConfig field though?
There is no 'vchConfig' tag available in the xml you shared. OR im missing something here ??
February 19, 2014 at 6:21 am
There is a table called tblInterface.
This table pulls back the following - running a SELECT -
SELECT TOP 1000 [intInterfaceId]
,[intConfigId]
,[vchName]
,[vchDescription]
,[vchConfig]
,[vchState]
,[bitPaused]
,[intScheduleType]
,[intScheduleOn]
,[intScheduleTime]
,[bitRunNow]
,[dteDateLastRun]
,[intUpdateUserId]
,[dteDateCreated]
,[dteDateUpdated]
,[imgInterfaceData]
FROM [Keystone_Train].[dbo].[tblInterface]
WHERE [intConfigId] = '4003'
For just the [vchConfig] field the following is within it -
<?xml version="1.0" encoding="utf-16"?> <InterfaceConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Source>.</Source> <Archive>.</Archive> <Log>.</Log> <Filter>PU*.csv</Filter> <Delimiter>"|"</Delimiter> <CreateAssets>true</CreateAssets> <UpdateAddresses>true</UpdateAddresses> <ManagementGroupId>1</ManagementGroupId> <ClearTenantRefusals>false</ClearTenantRefusals> <ClearTenantCharIds> <int>50</int> <int>51</int> <int>52</int> <int>53</int> <int>54</int> <int>55</int> <int>56</int> <int>57</int> <int>58</int> <int>59</int> <int>60</int> <int>61</int> <int>62</int> <int>63</int> <int>64</int> <int>65</int> <int>66</int> <int>67</int> <int>68</int> <int>69</int> <int>70</int> <int>74</int> <int>81</int> <int>85</int> <int>72</int> <int>73</int> <int>75</int> <int>76</int> <int>77</int> <int>78</int> <int>79</int> <int>80</int> <int>82</int> <int>83</int> <int>84</int> </ClearTenantCharIds> </InterfaceConfig>
How do I write something that after a restore of LIVE data to a TEST environment could I change what was in the Source, Archive and Log tags?
February 19, 2014 at 6:59 am
try this
declare @table table (id int identity(1,1), docxml xml)
insert into @table (docxml)
select CAST(N'<?xml version="1.0" encoding="utf-16"?> <InterfaceConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Source>.</Source> <Archive>.</Archive> <Log>.</Log> <Filter>PU*.csv</Filter> <Delimiter>"|"</Delimiter> <CreateAssets>true</CreateAssets> <UpdateAddresses>true</UpdateAddresses> <ManagementGroupId>1</ManagementGroupId> <ClearTenantRefusals>false</ClearTenantRefusals> <ClearTenantCharIds> <int>50</int> <int>51</int> <int>52</int> <int>53</int> <int>54</int> <int>55</int> <int>56</int> <int>57</int> <int>58</int> <int>59</int> <int>60</int> <int>61</int> <int>62</int> <int>63</int> <int>64</int> <int>65</int> <int>66</int> <int>67</int> <int>68</int> <int>69</int> <int>70</int> <int>74</int> <int>81</int> <int>85</int> <int>72</int> <int>73</int> <int>75</int> <int>76</int> <int>77</int> <int>78</int> <int>79</int> <int>80</int> <int>82</int> <int>83</int> <int>84</int> </ClearTenantCharIds> </InterfaceConfig>' as xml)
update @table
SET docxml.modify('replace value of (/InterfaceConfig/Source/text())[1]with ("this is a source")');
select * from @table
February 19, 2014 at 8:21 am
Hi there,
This isn't changing the table that I need it to though.
I have a table called tblInterface in a database called Keystone_Train.
There is a field in the table that is called vchConfig, which has xml in it. The data type is vchar(4000). Below is the example of the xml in that field.
How would a script change certain data within some of these tags. I need to change the Source, Archive and Log in the vchConfig field of the tblInterface table.
<?xml version="1.0" encoding="utf-16"?>
<InterfaceConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Source>.</Source>
<Archive>.</Archive>
<Log>.</Log>
<Filter>PU*.csv</Filter>
<Delimiter>"|"</Delimiter>
<CreateAssets>true</CreateAssets>
<UpdateAddresses>true</UpdateAddresses>
<ManagementGroupId>1</ManagementGroupId>
<ClearTenantRefusals>false</ClearTenantRefusals>
<ClearTenantCharIds>
<int>50</int>
<int>51</int>
<int>52</int>
<int>53</int>
<int>54</int>
<int>55</int>
<int>56</int>
<int>57</int>
<int>58</int>
<int>59</int>
<int>60</int>
<int>61</int>
<int>62</int>
<int>63</int>
<int>64</int>
<int>65</int>
<int>66</int>
<int>67</int>
<int>68</int>
<int>69</int>
<int>70</int>
<int>74</int>
<int>81</int>
<int>85</int>
<int>72</int>
<int>73</int>
<int>75</int>
<int>76</int>
<int>77</int>
<int>78</int>
<int>79</int>
<int>80</int>
<int>82</int>
<int>83</int>
<int>84</int>
</ClearTenantCharIds>
</InterfaceConfig>
Thanks
February 19, 2014 at 10:38 pm
you have to convert this nvarchar(4000) to xml 1st and then apply the code which i shared earlier. you cannot update the varchar field containing xml.
February 21, 2014 at 7:36 am
Thanks for your help. For those that want to use the following script, I ended up (with the help of someone else) with the following -
/************************************************************************************/
/*Enter the new paths for the source log and archive in the '' below*/
/*then save the script. You can then run the script after a backup has*/
/*been restored this will then replace whatever is currently between*/
/*the source, archive and log tags in the XML with the new source, archive*/
/*and log file specified.*/
/**/
/*THIS SCRIPT IS ONLY DESIGNED FOR THE PROPERTY INTERFACE. - 4003*/
/**/
/************************************************************************************/
USE Keystone_Train
--Declare variables to hold the new source, archive and log paths
DECLARE @NewSource varchar(8000)
DECLARE @NewArchive varchar(8000)
DECLARE @NewLog varchar(8000)
--Sets what the new source, archive and log paths are going to be
SET @NewSource = '\\mhsvi-testserver\source'
SET @NewArchive = '\\mhsvi-testserver\archive'
SET @NewLog = '\\mhsvi-testserver\log'
/************************************************************************************/
/*Do not edit the script below this point.*/
/************************************************************************************/
--Declares variables to hold the current source, archive and log paths
DECLARE @OldSource varchar(8000)
DECLARE @OldArchive varchar(8000)
DECLARE @OldLog varchar(8000)
--sets the variables to the current source, archive and log paths
SET @OldSource = (SELECT LEFT(RIGHT(vchConfig, LEN(vchConfig)-CHARINDEX('<Source>',vchConfig)+1), CHARINDEX('</Source>',RIGHT(vchConfig, LEN(vchConfig)-CHARINDEX('<source>',vchConfig)+1))+8) FROMtblInterface WHERE intConfigID = 4003)
SET @OldArchive = (SELECT LEFT(RIGHT(vchConfig, LEN(vchConfig)-CHARINDEX('<Archive>',vchConfig)+1), CHARINDEX('</Archive>',RIGHT(vchConfig, LEN(vchConfig)-CHARINDEX('<Archive>',vchConfig)+1))+9) FROMtblInterface WHERE intConfigID = 4003)
SET @OldLog = (SELECT LEFT(RIGHT(vchConfig, LEN(vchConfig)-CHARINDEX('<Log>',vchConfig)+1), CHARINDEX('</Log>',RIGHT(vchConfig, LEN(vchConfig)-CHARINDEX('<Log>',vchConfig)+1))+5) FROMtblInterface WHERE intConfigID = 4003)
--Updates tblInterface replacing the old source, archive and log paths with the new source, archive and log paths
UPDATE tblInterface
SET vchConfig = REPLACE(vchConfig,@OldSource, '<Source>'+@NewSource+'</Source>')
WHERE intConfigId = 4003
UPDATE tblInterface
SET vchConfig = REPLACE(vchConfig,@OldArchive, '<Archive>'+@NewArchive+'</Archive>')
WHERE intConfigId = 4003
UPDATE tblInterface
SET vchConfig = REPLACE(vchConfig,@OldLog, '<Log>'+@NewLog+'</Log>')
WHERE intConfigId = 4003
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply