Need to retrieve the column from table to xml structure

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  February 24, 2011 at 4:52 am

  #99187

  I have this select query,

  SELECT

  apcr_CredentialID,

  api_Name,

  apcr_ApiID,

  apcr_Field1Name,

  apcr_Field1Value,

  apcr_Field2Name,

  apcr_Field2Value,

  apcr_Field3Name,

  apcr_Field3Value,

  apcr_Field4Name,

  apcr_Field4Value,

  apcr_Field5Name,

  apcr_Field5Value,

  apcr_Field6Name,

  apcr_Field6Value,

  apcr_IsActive

  FROM dbo.ApiCredential AS AC

  JOIN dbo.Api AS A

  ON AC.apcr_ApiID = A.api_ApiID

  i want to display this field as xml tags

  like

  <Credentials>

  <Credential>

  <CredentialID>1100</CredentialID>

  <ApiName>ABC</ApiName>

  <ApiID>112</ApiID>

  <Parameter Name="UserID" Value="1%2*2" />

  <Parameter Name="ASD" Value="TYEYE" />

  <Parameter Name="vbb" Value="1%2*2" />

  <Parameter Name="dfd" Value="12121" />

  <Parameter Name="676" Value="3333" />

  <Parameter Name="222" Value="133" />

  </Credential>

  <Credential>

  <CredentialID>1101</CredentialID>

  <ApiName>XYZ</ApiName>

  <ApiID>112</ApiID>

  <Parameter Name="UserID" Value="1%2*2" />

  <Parameter Name="ASD" Value="TYEYE" />

  <Parameter Name="vbb" Value="1%2*2" />

  <Parameter Name="dfd" Value="12121" />

  <Parameter Name="676" Value="3333" />

  <Parameter Name="222" Value="133" />

  </Credential>

  <Credential>

  <CredentialID>1102</CredentialID>

  <ApiName>QQQ</ApiName>

  <ApiID>112</ApiID>

  <Parameter Name="UserID" Value="1%2*2" />

  <Parameter Name="ASD" Value="TYEYE" />

  <Parameter Name="vbb" Value="1%2*2" />

  <Parameter Name="dfd" Value="12121" />

  <Parameter Name="676" Value="3333" />

  <Parameter Name="222" Value="133" />

  </Credential>

  .....

  </Credentials>

  I had writte this code

  SELECT

  apcr_CredentialID AS 'Credential/CredentialID',

  api_Name AS 'Credential/ApiName',

  apcr_ApiID AS 'Credential/ApiID',

  apcr_Field1Name AS 'Credential/Parameter1/@Name',

  apcr_Field1Value AS 'Credential/Parameter1/@Value',

  apcr_Field2Name AS 'Credential/Parameter2/@Name',

  apcr_Field2Value AS 'Credential/Parameter2/@Value',

  apcr_Field3Name AS 'Credential/Parameter3/@Name',

  apcr_Field3Value AS 'Credential/Parameter3/@Value',

  apcr_Field4Name AS 'Credential/Parameter4/@Name',

  apcr_Field4Value AS 'Credential/Parameter4/@Value',

  apcr_Field5Name AS 'Credential/Parameter5/@Name',

  apcr_Field5Value AS 'Credential/Parameter5/@Value',

  apcr_Field6Name AS 'Credential/Parameter6/@Name',

  apcr_Field6Value AS 'Credential/Parameter6/@Value'

  FROM dbo.ApiCredential AS AC

  JOIN dbo.Api AS A

  ON AC.apcr_ApiID = A.api_ApiID

  FOR XML PATH(''),Root('Credentials'),TYPE

  In this I used different Parameter tag..like parameter1,2,3,4.

  This is the output from my code

  <Credentials>

  <Credential>

  <CredentialID>1100</CredentialID>

  <ApiName>ABC</ApiName>

  <ApiID>232</ApiID>

  <Parameter1 Name="UserID" Value="PCC7O9S" />

  <Parameter2 Name="Password" Value="##$#$" />

  <Parameter3 Name="dd" Value="@###" />

  <Parameter4 Name="sdd" Value="7O9S" />

  </Credential>

  <Credential>

  <CredentialID>1101</CredentialID>

  <ApiName>XYZ</ApiName>

  <ApiID>2333</ApiID>

  <Parameter1 Name="UserID" Value="7744" />

  <Parameter2 Name="Password" Value="JanUary10" />

  <Parameter3 Name="ddd" Value="dfdf" />

  <Parameter4 Name="ddd" Value="dfdf" />

  </Credential>

  <Credential>

  <CredentialID>1102</CredentialID>

  <ApiName>AAA</ApiName>

  <ApiID>111</ApiID>

  <Parameter1 Name="UserID" Value="erere" />

  <Parameter2 Name="Password" Value="SunnYdaY2" />

  <Parameter3 Name="rrr" Value="rtr" />

  <Parameter4 Name="rrr" Value="e" />

  </Credential>

  </Credentials>

  but i don't want to display Parameter tag as Parameter 1,2,3..i want to display it into acommon paramter tag

  These are the tables

  CREATE TABLE [dbo].[Api](

  [api_ApiID] [uniqueidentifier] NOT NULL PRimary KEY,

  [api_Name] [varchar](30) NOT NULL,

  [api_Assembly] [varchar](50) NOT NULL,

  [api_EntryClass] [varchar](50) NOT NULL,

  [api_Type] [int] NOT NULL,

  [api_DomesticOnly] [bit] NOT NULL,

  [api_CreditBased] [bit] NOT NULL,

  [api_CarrierCode] [varchar](3) NULL,

  [api_Flags] [bigint] NOT NULL,

  [api_IsActive] [bit] NOT NULL,

  [api_SubType] [int] NOT NULL,

  [api_IsEnabled] [bit] NOT NULL

  )

  GO

  CREATE TABLE [dbo].[ApiCredential](

  [apcr_CredentialID] [int] NOT NULL PRimary KEY,

  [apcr_ApiID] [uniqueidentifier] NOT NULL,

  [apcr_Field1Name] [varchar](40) NOT NULL,

  [apcr_Field1Value] [varchar](40) NOT NULL,

  [apcr_Field2Name] [varchar](40) NULL,

  [apcr_Field2Value] [varchar](40) NULL,

  [apcr_Field3Name] [varchar](40) NULL,

  [apcr_Field3Value] [varchar](40) NULL,

  [apcr_Field4Name] [varchar](40) NULL,

  [apcr_Field4Value] [varchar](40) NULL,

  [apcr_Field5Name] [varchar](40) NULL,

  [apcr_Field5Value] [varchar](40) NULL,

  [apcr_Field6Name] [varchar](40) NULL,

  [apcr_Field6Value] [varchar](40) NULL,

  [apcr_IsActive] [bit] NOT NULL

  )

  GO

  ALTER TABLE [dbo].[ApiCredential] ADD CONSTRAINT [FK_ApiCredential_Api] FOREIGN KEY([apcr_ApiID])

  REFERENCES [dbo].[Api] ([api_ApiID])

  GO

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 2, 2011 at 1:50 pm

  #1293728

  Can you please provide the DML to populate your tables with the data you're using in your examples?

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato