reading values from xml datatype in sql database

declare @test xml

DECLARE @ProvPayNoID int

set @test = '

'

SET @ProvPayNoID = @test.value('(/Fields/Field/@Value)[3]', 'int' )

SELECT @ProvPayNoID

declare @providerpayno int

select top 100 convert(xml,ExtraProperties).value('(/Fields/Field/@Value)[1]', 'int' ) as provpayno

from dbm.MedicalClaimsStaging

where BatchId='e5fff885-91bc-40a7-9350-5d176ca2fdfa'

declare @myDoc xml

DECLARE @ProdID int

set @myDoc = '

1 year parts and labor

3 year parts and labor extended maintenance is available

'

SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )

SELECT @ProdID

'

SET @ProvPayNoID = @test.value('(/Fields/Field/@Value)[3]', 'int' )
SELECT @ProvPayNoID

declare @providerpayno int

select top 100 convert(xml,ExtraProperties).value('(/Fields/Field/@Value)[1]', 'int' ) as provpayno
from mytable



declare @myDoc xml
DECLARE @ProdID int
set @myDoc = '


1 year parts and labor
3 year parts and labor extended maintenance is available


'
SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )
SELECT @ProdID

XML to XSD error

Error: Cannot have more than one type of file as input.

because the parameter sequence is not correct.

Parameter should be
nevigate to folder that has xml file

cd c:\xmltoxsdtest
type: XSD myxmlfile.xml
hit enter

create XSD file from XML file

using microsoft XSD.exe tool
1) go to command line (.Net command line)
2) cd path to where your xml file is located (cd c:\myxsdmapping)
3) type "XSD myxmlfile.xml
4) that it -- very simple

add pssnapin to computer all powershell session - powershell

go to
%systemroot%\System32\WindowsPowerShell\v1.0

2) Create profile.ps1 file
add following

[void][System.Reflection.Assembly]::LoadFile("C:\ProgramFiles\My.Powershell.DLL")
Add-PSSnapin -name "My.Powershell"

3) powershell global config

create powershell.exe.config file
configuration
appSettings
add key="SMTP_SERVER" value="mail.mydomain.com"
add key="email_from" value="HciPowershell@gmail.com"
add key="Working_Path" value="G:\AutoRun\"
add key="healthxhrafromemail" value="DevTeam@hciactive.com"
appSettings
configuration

rename table column using script

SP_RENAME '[datalens].[MyTable].[InOutPatientFlag]','IsInPatient','COLUMN'

Import XML data into Database

1) Open XML file into Excel (file > Open)
2) It will display Open XML dialog select "Use The XML Source task Pane"
3) Select Ok
4) It will XML schema on left side
5) Drag the XML schema to data area
6) save the Excel workbook as a template
7) open template workbook
8) right click on 1st cell
9) select import XML
10) give the source XML file
11) it will import only matching schema data.

using reflection to read nullable columns - type casting

CLICK HERE