CODEWITHSUNDEEP
sqlsql-server
user5950313
user5950313

Reputation:

formatting table

I have a table product that contains

Productcode  INformation

Infomration is XML type

<ArrayOfDonneesFormulaireType xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <DonneesFormulaireType LabelQuestion="Product" ValeurChamp="False" NomChamp="ClientHasAppointment" IsAffichable="false" />
  <DonneesFormulaireType LabelQuestion="Date" ValeurChamp="-" NomChamp="AppointmentCallingDay" IsAffichable="false" />
  <DonneesFormulaireType LabelQuestion="Hour" ValeurChamp="-" NomChamp="AppointmentCallingHour" IsAffichable="false" />
  </ArrayOfDonneesFormulaireType>

How can I get Table product like that

ProductCode   Product  Date Hour

I do it with oracle:

 dg.contenu_demande.extract('//DonneesFormulaireType[@NomChamp=''Product  '']/@ValeurChamp').getStringVal() as "ID_APPLICATION"

How to do ity the same within sql server?

Upvotes: 0

Views: 46

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8819

Use the nodes() method to query the Information XML then use the value() method to extract information from elements and attributes, e.g.:

create table dbo.Product (
  ProductCode nvarchar(50),
  Information xml
);

insert dbo.Product values
(N'Foo', N'<ArrayOfDonneesFormulaireType xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <DonneesFormulaireType LabelQuestion="Product" ValeurChamp="False" NomChamp="ClientHasAppointment" IsAffichable="false" />
  <DonneesFormulaireType LabelQuestion="Date" ValeurChamp="-" NomChamp="AppointmentCallingDay" IsAffichable="false" />
  <DonneesFormulaireType LabelQuestion="Hour" ValeurChamp="-" NomChamp="AppointmentCallingHour" IsAffichable="false" />
</ArrayOfDonneesFormulaireType>');

select
  [ProductCode],
  [Product] = x.n.value(N'(DonneesFormulaireType[@LabelQuestion="Product"]/@ValeurChamp)[1]', 'nvarchar(max)'),
  [Date] = x.n.value(N'(DonneesFormulaireType[@LabelQuestion="Date"]/@ValeurChamp)[1]', 'nvarchar(max)'),
  [Hour] = x.n.value(N'(DonneesFormulaireType[@LabelQuestion="Hour"]/@ValeurChamp)[1]', 'nvarchar(max)')
from dbo.Product
cross apply Information.nodes(N'/ArrayOfDonneesFormulaireType') x(n);

Which yields:

ProductCode  Product  Date  Hour
Foo          False    -     -

Upvotes: 1

Related Questions