Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 парсинг xml Скопировать значение из одного поля в другое  [new]
lex871
Member

Откуда:
Сообщений: 54
Здравствуйте, помогите пожалуйста с БД SQL.
В БД SQL данные хранятся в виде xml
Вот как в xml это хранится в БД
+

<r eid="b2b753f5-b7a8-4575-9a98-765bacc38f80"><revision>164418</revision><lastModifyNode null="1"></lastModifyNode><deleted>false</deleted><modified>2018-10-30T22:09:38.876+03:00</modified><created>2018-09-28T16:01:16.897+03:00</created><deletedDate>1900-01-01T00:00:00.000+03:00</deletedDate><userCreatedId null="1"></userCreatedId><userModifiedId null="1"></userModifiedId><localId>2471</localId><name><defaultResourceId null="1"></defaultResourceId><currentResourceId null="1"></currentResourceId><customValue>проверка123</customValue></name><description>для ценника Состав: говядина, яйцо, лук репчатый, лук зеленый перистый, горчица, масло растительное, горчица, перец черный молотый, соль 12 34 68</description><num>4356455602</num><parent>c6743c8d-3fcf-40b5-8bda-f75505f8bb2d</parent><code>9985</code><userCreated>339b0124-2818-471f-b4f9-d17a331f8891</userCreated><userModified>339b0124-2818-471f-b4f9-d17a331f8891</userModified><modifiers></modifiers><modifierSchema null="1"></modifierSchema><modifierSchemaRedefinitions null="1"></modifierSchemaRedefinitions><priceMarkupPercent null="1"></priceMarkupPercent><color null="1"></color><fontColor null="1"></fontColor><frontImageId null="1"></frontImageId><position null="1"></position><nds>0</nds><taxCategory null="1"></taxCategory><category null="1"></category><accountingCategory>824d69f2-ab0a-4757-9829-528dd34bed92</accountingCategory><markupSettings></markupSettings><isFixedPrice>false</isFixedPrice><customCategories></customCategories><producer null="1"></producer><producers></producers><alcoholClass null="1"></alcoholClass><egaisAlcCode null="1"></egaisAlcCode><minimalStoreBalanceLevels><k>1b91d7c5-506d-4cf1-8ecc-6d82e1422a1b</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v><k>6e226f01-1a5f-4b99-8a3a-d762ac84a434</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v><k>87a871dd-e9ad-420d-a9be-b19c1f1b852a</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v><k>a895fa8e-c13e-4d4a-8f77-499f91c8e2d2</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v><k>6b2e3f5a-f35d-4dd5-aa35-a6a31da79ad6</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v></minimalStoreBalanceLevels><defaultMinimumStoreBalanceLevel null="1"></defaultMinimumStoreBalanceLevel><maximumStoreBalanceLevels><k>1b91d7c5-506d-4cf1-8ecc-6d82e1422a1b</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v><k>6e226f01-1a5f-4b99-8a3a-d762ac84a434</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v><k>87a871dd-e9ad-420d-a9be-b19c1f1b852a</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v><k>a895fa8e-c13e-4d4a-8f77-499f91c8e2d2</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v><k>6b2e3f5a-f35d-4dd5-aa35-a6a31da79ad6</k><v><value null="1"></value><valueAssigned>true</valueAssigned></v></maximumStoreBalanceLevels><defaultMaximumStoreBalanceLevel null="1"></defaultMaximumStoreBalanceLevel><franchiseUniqueId null="1"></franchiseUniqueId><franchiseOriginalId null="1"></franchiseOriginalId><franchiseMasterId null="1"></franchiseMasterId><gtin null="1"></gtin><fullName></fullName><mainUnit>6040d92d-e286-f4f9-a613-ed0e6fd241e1</mainUnit><additionalUnits></additionalUnits><excludedSections null="1"></excludedSections><containers></containers><defaultContainerId null="1"></defaultContainerId><productScale null="1"></productScale><productSizeFactors null="1"></productSizeFactors><disabledProductSizes null="1"></disabledProductSizes><defaultSalePrice>1.000000000</defaultSalePrice><receiptsSum null="1"></receiptsSum><estimatedPurchasePrice>0E-9</estimatedPurchasePrice><placeType>0c702dde-416c-48c5-987e-4d14aeaddd99</placeType><inventoryPeriodDays>0</inventoryPeriodDays><nameEnglish></nameEnglish><fullNameEnglish>выход 1/150</fullNameEnglish><descriptionEnglish>#прейскуранта</descriptionEnglish><defaultIncludedInMenu>true</defaultIncludedInMenu><type>DISH</type><fatAmount>10.000000000</fatAmount><fiberAmount>2.000000000</fiberAmount><carbohydrateAmount>45.000000000</carbohydrateAmount><energyAmount>278.000000000</energyAmount><unitWeight>0.090090090</unitWeight><unitCapacity>0E-9</unitCapacity><isCalculateUnitWeight>true</isCalculateUnitWeight><notLoadAsemblyChartsToBackupDB>false</notLoadAsemblyChartsToBackupDB><productTypeForCooking null="1"></productTypeForCooking><cookingType>b53cac98-a375-74a0-928e-43718a5ae412</cookingType><images></images><isDishOfDay>false</isDishOfDay><isFlyerProgram>false</isFlyerProgram><canBuyFromCashdesk>false</canBuyFromCashdesk><isNotInStoreMovement>false</isNotInStoreMovement><invoiceSupplier null="1"></invoiceSupplier><prechequePrintable>true</prechequePrintable><chequePrintable>true</chequePrintable><useBalanceForInventory>false</useBalanceForInventory><useBalanceForSell>false</useBalanceForSell><useRangeForInvoices>false</useRangeForInvoices><isTimePayProduct>false</isTimePayProduct><barcodes null="1"></barcodes><expirationDate null="1"></expirationDate><assemblyChartModified>2018-10-04T13:43:04.227+03:00</assemblyChartModified><cookWithMainDish>true</cookWithMainDish><sourceNature null="1"></sourceNature><rateSchedule null="1"></rateSchedule><relativeRateSchedule>false</relativeRateSchedule><minimumTimePayProductDurationMinutes null="1"></minimumTimePayProductDurationMinutes><timePayProductDurationStepMinutes>1</timePayProductDurationStepMinutes><printOnAdd>false</printOnAdd><coldLossPercent>0</coldLossPercent><hotLossPercent>0</hotLossPercent><amountIndependentOfParentAmount>false</amountIndependentOfParentAmount><nameKitchen></nameKitchen><cookingTimeNormal null="1"></cookingTimeNormal><cookingTimePeak null="1"></cookingTimePeak><useDefaultCookingTime>true</useDefaultCookingTime><canSetOpenPrice>false</canSetOpenPrice><canChangeAmountCookedDish>false</canChangeAmountCookedDish><allergenGroups null="1"></allergenGroups><percentageOfAlcohol>0</percentageOfAlcohol><isPercentageOfAlcoholApplicable>false</isPercentageOfAlcoholApplicable><disabledFields null="1"></disabledFields></r>


если запросом сделать cast(xml as xml),
то данные предстают в таком виде
+

<r eid="b2b753f5-b7a8-4575-9a98-765bacc38f80">
  <revision>164418</revision>
  <lastModifyNode null="1" />
  <deleted>false</deleted>
  <modified>2018-10-30T22:09:38.876+03:00</modified>
  <created>2018-09-28T16:01:16.897+03:00</created>
  <deletedDate>1900-01-01T00:00:00.000+03:00</deletedDate>
  <userCreatedId null="1" />
  <userModifiedId null="1" />
  <localId>2471</localId>
  <name>
    <defaultResourceId null="1" />
    <currentResourceId null="1" />
    <customValue>проверка123</customValue>
  </name>
  <description>для ценника Состав: говядина, яйцо, лук репчатый, лук зеленый перистый, горчица, масло растительное, горчица, перец черный молотый, соль
12
34
68</description>
  <num>4356455602</num>
  <parent>c6743c8d-3fcf-40b5-8bda-f75505f8bb2d</parent>
  <code>9985</code>
  <userCreated>339b0124-2818-471f-b4f9-d17a331f8891</userCreated>
  <userModified>339b0124-2818-471f-b4f9-d17a331f8891</userModified>
  <modifiers />
  <modifierSchema null="1" />
  <modifierSchemaRedefinitions null="1" />
  <priceMarkupPercent null="1" />
  <color null="1" />
  <fontColor null="1" />
  <frontImageId null="1" />
  <position null="1" />
  <nds>0</nds>
  <taxCategory null="1" />
  <category null="1" />
  <accountingCategory>824d69f2-ab0a-4757-9829-528dd34bed92</accountingCategory>
  <markupSettings />
  <isFixedPrice>false</isFixedPrice>
  <customCategories />
  <producer null="1" />
  <producers />
  <alcoholClass null="1" />
  <egaisAlcCode null="1" />
  <minimalStoreBalanceLevels>
    <k>1b91d7c5-506d-4cf1-8ecc-6d82e1422a1b</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
    <k>6e226f01-1a5f-4b99-8a3a-d762ac84a434</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
    <k>87a871dd-e9ad-420d-a9be-b19c1f1b852a</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
    <k>a895fa8e-c13e-4d4a-8f77-499f91c8e2d2</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
    <k>6b2e3f5a-f35d-4dd5-aa35-a6a31da79ad6</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
  </minimalStoreBalanceLevels>
  <defaultMinimumStoreBalanceLevel null="1" />
  <maximumStoreBalanceLevels>
    <k>1b91d7c5-506d-4cf1-8ecc-6d82e1422a1b</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
    <k>6e226f01-1a5f-4b99-8a3a-d762ac84a434</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
    <k>87a871dd-e9ad-420d-a9be-b19c1f1b852a</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
    <k>a895fa8e-c13e-4d4a-8f77-499f91c8e2d2</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
    <k>6b2e3f5a-f35d-4dd5-aa35-a6a31da79ad6</k>
    <v>
      <value null="1" />
      <valueAssigned>true</valueAssigned>
    </v>
  </maximumStoreBalanceLevels>
  <defaultMaximumStoreBalanceLevel null="1" />
  <franchiseUniqueId null="1" />
  <franchiseOriginalId null="1" />
  <franchiseMasterId null="1" />
  <gtin null="1" />
  <fullName />
  <mainUnit>6040d92d-e286-f4f9-a613-ed0e6fd241e1</mainUnit>
  <additionalUnits />
  <excludedSections null="1" />
  <containers />
  <defaultContainerId null="1" />
  <productScale null="1" />
  <productSizeFactors null="1" />
  <disabledProductSizes null="1" />
  <defaultSalePrice>1.000000000</defaultSalePrice>
  <receiptsSum null="1" />
  <estimatedPurchasePrice>0E-9</estimatedPurchasePrice>
  <placeType>0c702dde-416c-48c5-987e-4d14aeaddd99</placeType>
  <inventoryPeriodDays>0</inventoryPeriodDays>
  <nameEnglish />
  <fullNameEnglish>выход 1/150</fullNameEnglish>
  <descriptionEnglish>#прейскуранта</descriptionEnglish>
  <defaultIncludedInMenu>true</defaultIncludedInMenu>
  <type>DISH</type>
  <fatAmount>10.000000000</fatAmount>
  <fiberAmount>2.000000000</fiberAmount>
  <carbohydrateAmount>45.000000000</carbohydrateAmount>
  <energyAmount>278.000000000</energyAmount>
  <unitWeight>0.090090090</unitWeight>
  <unitCapacity>0E-9</unitCapacity>
  <isCalculateUnitWeight>true</isCalculateUnitWeight>
  <notLoadAsemblyChartsToBackupDB>false</notLoadAsemblyChartsToBackupDB>
  <productTypeForCooking null="1" />
  <cookingType>b53cac98-a375-74a0-928e-43718a5ae412</cookingType>
  <images />
  <isDishOfDay>false</isDishOfDay>
  <isFlyerProgram>false</isFlyerProgram>
  <canBuyFromCashdesk>false</canBuyFromCashdesk>
  <isNotInStoreMovement>false</isNotInStoreMovement>
  <invoiceSupplier null="1" />
  <prechequePrintable>true</prechequePrintable>
  <chequePrintable>true</chequePrintable>
  <useBalanceForInventory>false</useBalanceForInventory>
  <useBalanceForSell>false</useBalanceForSell>
  <useRangeForInvoices>false</useRangeForInvoices>
  <isTimePayProduct>false</isTimePayProduct>
  <barcodes null="1" />
  <expirationDate null="1" />
  <assemblyChartModified>2018-10-04T13:43:04.227+03:00</assemblyChartModified>
  <cookWithMainDish>true</cookWithMainDish>
  <sourceNature null="1" />
  <rateSchedule null="1" />
  <relativeRateSchedule>false</relativeRateSchedule>
  <minimumTimePayProductDurationMinutes null="1" />
  <timePayProductDurationStepMinutes>1</timePayProductDurationStepMinutes>
  <printOnAdd>false</printOnAdd>
  <coldLossPercent>0</coldLossPercent>
  <hotLossPercent>0</hotLossPercent>
  <amountIndependentOfParentAmount>false</amountIndependentOfParentAmount>
  <nameKitchen />
  <cookingTimeNormal null="1" />
  <cookingTimePeak null="1" />
  <useDefaultCookingTime>true</useDefaultCookingTime>
  <canSetOpenPrice>false</canSetOpenPrice>
  <canChangeAmountCookedDish>false</canChangeAmountCookedDish>
  <allergenGroups null="1" />
  <percentageOfAlcohol>0</percentageOfAlcohol>
  <isPercentageOfAlcoholApplicable>false</isPercentageOfAlcoholApplicable>
  <disabledFields null="1" />
</r>



Помогите пожалуйста, как можно скопировать значение "выход 1/150"
<fullNameEnglish>выход 1/150</fullNameEnglish>
и добавить его в поле description
  <description>для ценника Состав: говядина, яйцо, лук репчатый, лук зеленый перистый, горчица, масло растительное, горчица, перец черный молотый, соль
12
34
68</description>

в самый конец, но только с переносом на новую строчку....
Горит задание, руками перебивать очень много....
30 окт 18, 22:16    [21719594]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml Скопировать значение из одного поля в другое  [new]
lex871
Member

Откуда:
Сообщений: 54
написал примерный запрос, но не могу пока решить вот в этом месте
set @myDoc.modify('insert value before (r/description)[1] with sql:variable("@bar1")')
ошибка
Сообщение 2370, уровень 16, состояние 1, строка 23
XQuery [modify()]: В конце выражения XQuery не ожидались дополнительные токены. Обнаружено "with".

+

DECLARE @id uniqueidentifier, @myDoc xml, @bar1 XML;
 
DECLARE user_cursor CURSOR FOR
SELECT id--, Name,Num,FastCode,TYPE,MeasuringUnit 
FROM dbo.entity
WHERE  ID  in (select id from entity where type='Product' and xml like '%</fullNameEnglish>%') ;
 
OPEN user_cursor;
 
FETCH NEXT FROM user_cursor
INTO @id;
 
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN


   BEGIN
       SET @myDoc = (select CAST([xml] as XML) from entity where id=@id)
       set @bar1= (select CAST(cast(xml as xml).query('/r/fullNameEnglish/node()') as nvarchar(max)) from Resto.dbo.entity where id = @id)

set @myDoc.modify('insert value before (r/description)[1] with sql:variable("@bar1")')

       update dbo.entity
       set xml = CONVERT( nvarchar(max) ,@myDoc)
	   , revision=( select revision from dbversion)
	   where id = @id
	  
   END
 
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM user_cursor
   INTO @id;
END
 
CLOSE user_cursor;
DEALLOCATE user_cursor;
30 окт 18, 22:47    [21719604]     Ответить | Цитировать Сообщить модератору
 Re: парсинг xml Скопировать значение из одного поля в другое  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3247
lex871,

'insert sql:variable("@bar1") before (r/description)[1]'
Как минимум, возможно под спойлером что-то еще. Не изобретайте свой синтаксис, сервер вас не поймет.
31 окт 18, 03:15    [21719728]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить