February 18, 2011 at 7:24 am
I have attached a sample database (forum) that contains a single table 'Audit'. The table has 3 records. The first record is the creation of the customer record. The second record is where I added some text to a 'Notes' field. The third record is where I added a fax number. The changes are stored in fields 'AuditXML', 'AuditXMLNew'.
I would like a query that returns only the changes. from withhin the XML string.
Query when executed would look as follows:
ChangeID.....Value Changed---Value Before.....Value After.................Modified By
.....1..............Notes................NULL.............'I added a note'............Joe Bloggs
.....1..............Fax...................NULL.............'0012586526'...............Joe Bloggs
I would appreciate any help with this one.
Kind regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 18, 2011 at 3:41 pm
Back again. How can I query column 'AuditXML' to list individual element values (i.e. extract CreatedUser, LastModifiedUser). The column data type is nvarchar (max)?
<Audit><PrimaryTable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Customer>70798</Customer><CustomerId>C00237</CustomerId><Branch>0000</Branch><CustomerName>SQL Central</CustomerName><Address>In the Cloud</Address><City>City</City><Region></Region><PostalCode></PostalCode><Country>229</Country><PhoneNumber>0012586525</PhoneNumber><FaxNumber></FaxNumber><EmailAddress>info@sqlcentral.com</EmailAddress><WebSite>http://www.sqlservercentral.com/</WebSite><Industry>216</Industry><Currency>292</Currency><GLAccountDivision>2</GLAccountDivision><Department>25</Department><TaxCode>23</TaxCode><TaxNumber></TaxNumber><Bank>148</Bank><CreditTerms>332</CreditTerms><Discount>408</Discount><CreditLimit xsi:nil="true"/><GLAccountType>2718</GLAccountType><PriceList xsi:nil="true"/><CertificateOfConformity>0</CertificateOfConformity><Contact></Contact><Notes></Notes><TextFile></TextFile><Active>1</Active><TradingStatus>T</TradingStatus><TheirIdentifier></TheirIdentifier><SettlementTerms>1</SettlementTerms><Area xsi:nil="true"/><Billing>1</Billing><DC_001_TXT></DC_001_TXT><DC_002_TXT></DC_002_TXT><DC_003_TXT></DC_003_TXT><DC_004_TXT></DC_004_TXT><DC_005_INT>0</DC_005_INT><DC_006_DAT>1901-01-01T00:00:00</DC_006_DAT><CreatedUser>Joe Bloggs</CreatedUser><CreatedDate>2011-02-18T12:23:46.180</CreatedDate><LastModifiedUser>Joe Bloggs</LastModifiedUser><LastModifiedDate>2011-02-18T12:23:46.230</LastModifiedDate><Comments></Comments><DeliveryUseDefault>1</DeliveryUseDefault><DeliveryAddress>5970</DeliveryAddress><CustomerPricingClassification xsi:nil="true"/><Site xsi:nil="true"/><TaxCodeSecondary xsi:nil="true"/><ConsignmentLocation xsi:nil="true"/><PromptText></PromptText><Uplift1>0</Uplift1><Uplift2>0</Uplift2><FreeTextGLType xsi:nil="true"/><CommissionClass xsi:nil="true"/><PhoneNumberRaw>0012586525</PhoneNumberRaw><PickingLocation xsi:nil="true"/><AveragePayDaysLate>0</AveragePayDaysLate><CustomerGUID>E26B32AE-4269-49E1-B4C1-332D3658A957</CustomerGUID><ShelfLifeGuaranteeFactor>N</ShelfLifeGuaranteeFactor><ShelfLifeGuaranteeValue>0</ShelfLifeGuaranteeValue><PreferredShelfLifeFactor>N</PreferredShelfLifeFactor><PreferredShelfLifeValue>0</PreferredShelfLifeValue><B2B>1</B2B><CustomerAlias></CustomerAlias></PrimaryTable></Audit>
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 21, 2011 at 8:12 am
Hi. I now have a report. Datset as follows:
DECLARE @idoc int
DECLARE @AuditXML nvarchar(max)
DECLARE @PrimaryTableName NVARCHAR(25)
SET @PrimaryTableName = 'Customers'
DECLARE @Identifier int
SET @Identifier = 70798
DECLARE @TextId NVARCHAR(20)
SET @TextId = 'C00237'
SELECT Audit,
CASE WHEN patindex('Audit%',AuditCode) > 0 THEN substring(AuditCode,7,len(AuditCode) - 6)
ELSE AuditCode END AS AuditCode,
AuditUser,AuditDate,AuditXML,TextId,Identifier,AuditXMLNew
FROM Audit
WHERE TableName = @PrimaryTableName and (Identifier = @Identifier or TextId = @TextId)
The report contains the following expressions:
1) =code.DecodeXML("PrimaryTable",Fields!AuditXML.Value)
2) =code.DecodeXML("PrimaryTable",Fields!AuditXMLNew.Value)
1) Shows field values prior to change.
2) Shows field values after change.
I will ask this question here but am happy to move to 'Reports' forum.
I would like to display values in RED where the values between 1 and 2 differ in order to highlight any change.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 22, 2011 at 9:33 am
It transpires the report has the following code:
public function DecodeXML(byval rootNode as string,byval myXML as string) as string
try
Dim str As New System.IO.StringReader(myXML)
Dim r As System.Xml.XmlReader = System.Xml.XmlReader.Create(str)
r.Read()
r.ReadToFollowing(rootNode)
r = r.ReadSubTree
r.Read()
dim decoded as string = ""
While r.EOF = False
r.Read()
If r.NodeType = Xml.XmlNodeType.Element Then
if decoded = "" then
decoded = r.Name & ": "
else
decoded = decoded & vbcrlf & r.Name & ": "
end if
end if
If r.NodeType = Xml.XmlNodeType.Text Then decoded = decoded & r.ReadString
End While
return decoded
catch ex as exception
end try
end function
I have 2 fields on the report:
Column A......................................................................Column B
=code.DecodeXML("PrimaryTable",Fields!AuditXML.Value).......=code.DecodeXML("PrimaryTable",Fields!AuditXMLNew.Value)
I would like to highlight differences between Column A and Column B in 'Red' font.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply