Question

Shreyanshi on Tue, 05 Apr 2016 12:07:14


Hi

I would like to set the value of the boolean column to "True" in SQL table but its related attribute is not there in XML data file.

XML data file is as follows:

<?xml version="1.0" encoding="utf-8"?>
<ILHData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<a v="Blatt 1 (07/98)" n="AM-Center Kö. 45 1. OG" id="R4Nx" p="11/11" s="3. Hygiene-Folgeinspektion" t="2" d="2012-10-23" c="2012-10-	18T15:36:47.298Z" ll="10.000" as="Technikraum" l="2012-11-27T12:35:29.514Z" j="1993" h="AL-KO Therm GmbH" i="Dipl.-Ing. (FH) M. Kirste, Techn.         H. Franke" r="AM-Center" z="10007782" w="HSG Zander GmbH" b="Herr Töpferwein">
		<p>
			<f n="DSC02610.jpg" id="ZALA"/>
		</p>
	</a>
</ILHData>	

XML Schema file is as follows:

<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema" xmlns:sql = "urn:schemas-microsoft-com:mapping-schema">
  <xsd:annotation>
    <xsd:appinfo>
      <sql:relationship name = "InspektiontoAnlagen_Foto"
                       parent= "tblInspektion"
                       parent-key = "Id"
                       child= "tblAnlagen_Foto"
                       child-key= "InspektionId" />
    </xsd:appinfo>
  </xsd:annotation>
  <xsd:element name="ILHData" sql:is-constant="1" >
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="a" sql:relation ="tblInspektion">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="p" sql:is-constant="1">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element name="f" sql:relation ="tblAnlagen_Foto" sql:relationship="InspektiontoAnlagen_Foto">
                      <xsd:complexType>
                        <xsd:attribute name="id" type="xsd:string" sql:field = "Id" />
                        <xsd:attribute name ="n" type = "xsd:string" sql:field = "Dateiname" />
                        <xsd:attribute name ="af" type = "xsd:boolean" sql:field = "IsAnlagefoto" fixed="true"/>
                      </xsd:complexType>
                    </xsd:element>
                  </xsd:sequence>
                </xsd:complexType >  
              </xsd:element >  
            </xsd:sequence> 
            <xsd:attribute name="id" type="xsd:string" sql:field = "Id" />
            <xsd:attribute name ="n" type = "xsd:string" sql:field = "Name" />
            <xsd:attribute name ="i" type = "xsd:string" sql:field = "Inspektoren" />
            <xsd:attribute name ="v" type = "xsd:string" sql:field = "VDI" />
            <xsd:attribute name ="s" type = "xsd:string" sql:field = "Stufe" />
            <xsd:attribute name ="d" type = "xsd:string" sql:field = "Inspektionsdatum" />
            <xsd:attribute name ="ll" type = "xsd:string" sql:field = "Luftleistung" />
            <xsd:attribute name ="m" type = "xsd:string" sql:field = "Baumonat" />
            <xsd:attribute name ="j" type = "xsd:string" sql:field = "Baujahr" />
            <xsd:attribute name ="h" type = "xsd:string" sql:field = "Hersteller" />
            <xsd:attribute name ="r" type = "xsd:string" sql:field = "Räume" />
            <xsd:attribute name ="w" type = "xsd:string" sql:field = "WartungVon" />
            <xsd:attribute name ="p" type = "xsd:string" sql:field = "WartungDatum" />
            <xsd:attribute name ="as" type = "xsd:string" sql:field = "Standort" />
            <xsd:attribute name ="dt" type = "xsd:string" sql:field = "Tiefe" />
            <xsd:attribute name ="dh" type = "xsd:string" sql:field = "Höhe" />
            <xsd:attribute name ="db" type = "xsd:string" sql:field = "Breite" />
            <xsd:attribute name ="z" type = "xsd:string" sql:field = "KundeId" />
            <xsd:attribute name ="b" type = "xsd:string" sql:field = "Begleiter" />
            <xsd:attribute name ="k" type = "xsd:string" sql:field = "AnlageKategorie" />
            <xsd:attribute name ="t" type = "xsd:string" sql:field = "AnlageTyp" />
          </xsd:complexType>
        </xsd:element>
				</xsd:sequence>
				</xsd:complexType>
				</xsd:element>
</xsd:schema>

Structure of SQL table is as follows:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblAnlagen_Foto](
	[ID] [char](10) NOT NULL,
	[Dateiname] [varchar](100) NULL,
	[Beschreibung] [nvarchar](max) NULL,
	[VorläufigerName] [bit] NULL,
	[PositionInText] [int] NULL,
	[TextSubPosition] [int] NULL,
	[AutoFilename] [bit] NULL,
	[BefundeId] [varchar](50) NULL,
	[InspektionId] [char](10) NULL,
	[Status] [int] NULL,
	[Bild] [image] NULL,
	[IsAnlagefoto] [bit] NULL,
	[Createdby] [bigint] NULL,
	[CreatedDatetime] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
	[ModifiedDatetime] [datetime] NULL,
 CONSTRAINT [PK_tblAnlagen_Foto] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblAnlagen_Foto]  WITH NOCHECK ADD  CONSTRAINT [FK_tblAnlagen_Foto_tblBefund] FOREIGN KEY([BefundeId])
REFERENCES [dbo].[tblBefund] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblAnlagen_Foto] CHECK CONSTRAINT [FK_tblAnlagen_Foto_tblBefund]
GO

ALTER TABLE [dbo].[tblAnlagen_Foto]  WITH CHECK ADD  CONSTRAINT [FK_tblAnlagen_Foto_tblInspektion] FOREIGN KEY([InspektionId])
REFERENCES [dbo].[tblInspektion] ([Id])
GO

ALTER TABLE [dbo].[tblAnlagen_Foto] CHECK CONSTRAINT [FK_tblAnlagen_Foto_tblInspektion]
GO

in Schema file , i have added following

<xsd:attribute name ="af" type = "xsd:boolean" sql:field = "IsAnlagefoto" fixed="true"/>

but it shows me error: a mapping cannot be defined on a fixed node 'af'

How to assign value to the column "IsAnlageFoto" in tblAnlagen_foto, when there is no data specified in XML data file?

thanks,

Shreyanshi



Sponsored



Replies

Eric__Zhang on Wed, 06 Apr 2016 02:48:40


Hi Shreyanshi,

I can't test your sample in my environment as it raises some errors.

Anyway to set the value of the bit column to "True" in SQL table but its related attribute is not there in XML data file, you can do that in SQL Server end. Any row that comes into the table without specifying any value for IsAnlagefoto will be specified the default value 1.

ALTER TABLE
tblAnlagen_Foto
ADD CONSTRAINT [IsAnlagefoto_DEFAULT] DEFAULT(1) FOR [IsAnlagefoto] 

If you have any question, feel free to let me know.

Eric Zhang
TechNet Community Support

Shreyanshi on Wed, 06 Apr 2016 07:41:21


hi

in this same table, i would like to make entry using other XML tag - apart from following tag. In that case the value of IsAnlagefoto field should be false. 

<p>
	<f n="DSC02610.jpg" id="ZALA"/>
</p>


Here is table structure:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblAnlagen_Foto](
	[ID] [char](10) NOT NULL,
	[Dateiname] [varchar](100) NULL,
	[Beschreibung] [nvarchar](max) NULL,
	[VorläufigerName] [bit] NULL,
	[PositionInText] [int] NULL,
	[TextSubPosition] [int] NULL,
	[AutoFilename] [bit] NULL,
	[BefundeId] [varchar](50) NULL,
	[InspektionId] [char](10) NULL,
	[Status] [int] NULL,
	[Bild] [image] NULL,
	[IsAnlagefoto] [bit] NULL,
	[Createdby] [bigint] NULL,
	[CreatedDatetime] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
	[ModifiedDatetime] [datetime] NULL,
 CONSTRAINT [PK_tblAnlagen_Foto] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblAnlagen_Foto]  WITH CHECK ADD  CONSTRAINT [FK_tblAnlagen_Foto_tblInspektion] FOREIGN KEY([InspektionId])
REFERENCES [dbo].[tblInspektion] ([Id])
GO

ALTER TABLE [dbo].[tblAnlagen_Foto] CHECK CONSTRAINT [FK_tblAnlagen_Foto_tblInspektion]
GO

Is it possible to set value of IsAnlagefoto in schema file? 

Thanks 

Shreyanshi