SQL XML

INSERT INTO  BL_NetworkMembers
   (ContactID, NetworksID) 
  SELECT
   Contacts.ContactID,
   Network.NetworksID
  FROM (
    SELECT
    NetworkCol.value(‘../../Email[1]‘,’VARCHAR(150)’) AS Email ,
    NetworkCol.value(‘../../UID[1]‘,’bigint’) AS UID,
    NetworkCol.value(‘NetworkID[1]‘,’int’) as NetworksID
    FROM @XMLString.nodes(‘//Contacts/Contact/Networks/Network’)  tabNetwork(NetworkCol)
    )Network
  JOIN @ContactTemp Contacts
  ON  Contacts.Email = Network.Email
  AND  Contacts.UID = Network.UID
  WHERE ISNULL(Network.NetworksID,”) <>”
==================================================================
SELECT  tab.col.value(”(..//../UID)[1]”,”bigint”) AS UID,
 tab.col.value(”ChildName[1]”,”VARCHAR(100)”) AS ChildName,
 tab.col.value(”ChildDOB[1]”,”SMALLDATETIME”) AS ChildDOB
 FROM @XMLString.nodes(”/TestInfo/MyChildren/MyChild”) tab(col)

 

–<TestInfo>
–<UID>45</UID>
–<DOB>1923-03-27T00:00:00</DOB>
–<Relegion>29</Relegion>
–<RaceEthinicity>4</RaceEthinicity>
–<AnnualIncome>14</AnnualIncome>
–<Gender>1</Gender>
–<RelationShipStatus>26</RelationShipStatus>
–<AnniversaryDate>April/DD/1931</AnniversaryDate>
–<SpouceDOB>March/DD/1933</SpouceDOB>
–<Children>2</Children>
– <MyChildren>
– <MyChild>
–<ChildName>test</ChildName>
–<ChildDOB>1908-03-07T00:00:00</ChildDOB>
–</MyChild>
– <MyChild>
–<ChildName>asdfaf</ChildName>
–<ChildDOB>1923-05-27T00:00:00</ChildDOB>
–</MyChild>
–</MyChildren>
– <MyLanguages>
– <MyLanguage>
–<LanguageID>1</LanguageID>
–<LanguageName>English</LanguageName>
–</MyLanguage>
– <MyLanguage>
–<LanguageID>2</LanguageID>
–<LanguageName>Chinese</LanguageName>
–</MyLanguage>
– <MyLanguage>
–<LanguageID>3</LanguageID>
–<LanguageName>Spanish</LanguageName>
–</MyLanguage>
–</MyLanguages>
–</TestInfo>
ROOT,path
————-

select
 UserDetails.UserName,
 UserDetails.AddressLine1,
 UserDetails.AddressLine2,
 UserDetails.UserID,
 states=(select  state,stateid from state for xml path(‘State’),type)
from  UserDetails 
for xml path (‘Record’), ROOT(‘Records’)
=================================================

Get all values from node ,not the first one
—————————————–

select UserID=@LoginUserID,FieldName=’Landline1′,SharingType=2,
   tab.col.value(‘(StatusContact/text())[1]‘,’VARCHAR(25)’) AS Active ,
   isnull(tab.col.value(‘(Id/text())[1]‘,’VARCHAR(25)’),0) AS sort
   FROM @XMLString.nodes(‘MingleCard/StatusCollections/Status/LandLine1/ContactStatus’)  tab(col)
   —-where tab.col.value(‘LandLine1Status[1]‘,’VARCHAR(25)’)=1

……
…….
<landline1>
- <ContactStatus>
  <StatusContact>1</StatusContact>
  <Id>1</Id>
  </ContactStatus>
- <ContactStatus>
  <StatusContact>1</StatusContact>
  <Id>2</Id>
  </ContactStatus>
- <ContactStatus>
  <StatusContact>1</StatusContact>
  <Id>3</Id>
  </ContactStatus>
  </landline1>

…….

 

===================================================================
 DATALENGTH
===================================================================
  SET @XMLROWCOUNT = (SELECT Result=”true”,
   WorkExperienceID=isnull(WorkExperienceID,””),
   CompanyName=isnull(CompanyName,””),WebsiteUrl=isnull(WebsiteUrl,””),IndustryID=isnull(IndustryID,””),
   CompanyServices=isnull(Services,””),
   StreetAddress=isnull(StreetAddress,””),City=isnull(City,””),Zipcode=isnull(Zipcode,””),JobTitle=isnull(JobTitle,””),
   CountryID=isnull(MingleCountryCode,””)
  FROM dbo.U_MyWorkExperience
  WHERE UID=@loginId and ProfessionalInfo=1
  FOR XML PATH(”ProfessionalInformation”))

 
   IF DATALENGTH(@XMLROWCOUNT) > 0
  BEGIN
   SELECT @XMLROWCOUNT
  END
  ELSE
  BEGIN
   SELECT Result=”true”,MingleCountryCode AS CountryID FROM U_MyRegistration WHERE UserID = @loginId FOR XML PATH(”ProfessionalInformation”)
   –Select Result = ”false” FOR XML PATH(”ProfessionalInformation”)
  END
—————————-@XMLResult.Modify xml—————————————————–
SELECT @RecCount = @XMLResult.value(‘count(/AccountsList/Records/Record/RowNum[1])’,'varchar(10)’)

set @XMLResult.modify(‘replace value of (/AccountsList[1]/Count/text())[1] with sql:variable(“@RecCount”)’)

——————————xml custom structure———————————-

declare @XMLResult xml
SET @XMLResult = (SELECT Result=’true’,
WorkExperienceID=isnull(WorkExperienceID,”),
MyJobTitle=isnull(JobTitle,”),
isnull(CompanyName,”) AS ‘Company/Name’,
isnull(WebsiteUrl,”) AS ‘Company/WebSite’,
isnull(Services,”) AS ‘Company/Services’,
isnull(IndustryID,”) AS ‘Company/Industry/IndustryID’,
(SELECT IndustryName FROM BL_IndustryTypes WHERE IndustryID = U_MyWorkExperience.IndustryID) AS ‘Company/Industry/IndustryName’,
isnull(StreetAddress,”) AS ‘Company/CompanyAddress/StreetAddress1′,
isnull(City,”) AS ‘Company/CompanyAddress/Country/CityName’,
isnull(MingleCountryCode,”) AS ‘Company/CompanyAddress/Country/CountryID’,
(SELECT CountryName FROM BL_Country WHERE MingleCountryCode = U_MyWorkExperience.MingleCountryCode) AS ‘Company/CompanyAddress/Country/CountryName’,
isnull(CompanyEmail,”) AS ‘Company/CompanyEmail/Email’,
isnull(CompanyPhone,”) AS ‘Company/CompanyPhone/Phone’
–City=isnull(City,”),Zipcode=isnull(Zipcode,”)
FROM dbo.U_MyWorkExperience
WHERE LoginUserID=2 and ProfessionalInfo=1
FOR XML PATH(‘ProfessionalInformation’))
select @XMLResult
=———————————————————————————-
OUTPUT
=———————————————————————————-

 declare @T table ( id bigint)
 insert   into w_sponsor(userid,SponsorLogo)
  OUTPUT Inserted.sponsorid into  @T 
 values (1,’1′)
 
=—-
 declare @T table ( id bigint)
 update w_sponsor set userid=1
 OUTPUT Inserted.sponsorid into  @T  where sponsorid >10
 
select * from  @T
=======================
CTE
=======================
WITH CTE_Orders AS

(

SELECT Orderid, Shipname, shippeddate ,ROW_NUMBER() OVER (ORDER BY shippeddate DESC) AS RowNumber

FROM orders

)

SELECT Orderid, Shipname, shippeddate FROM CTE_Orders WHERE RowNumber BETWEEN 1 AND 30  ORDER BY RowNumber ASC;

 

2) Makes querying hierarchical data easier

WITH sql_cte (emp)
AS (
 SELECT emp FROM sql_org WHERE mgr = ‘Joe’
UNION ALL
 SELECT a.emp FROM sql_org a JOIN sql_cte b ON b.emp = a.mgr )
SELECT * FROM sql_cte
=============================================
Error handling
=====================================================
BEGIN TRY
 – Generate a constraint violation error.
 DELETE FROM Production.Product
 WHERE ProductID = 980;
END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER() AS ErrorNumber,
  ERROR_SEVERITY() AS ErrorSeverity,
  ERROR_STATE() as ErrorState,
  ERROR_MESSAGE() as ErrorMessage;
END CATCH

———————–update method ———————————————
UPDATE C SET C.FName = A.[First Name], C.LName=A.[Last Name],
   C.SourceType = A.SourceType,
   C.SourceTag = A.SourceTag   
  FROM U_MyContacts C
  INNER JOIN Mingle_ImportAddress.dbo.BL_ImportAddressForUpadte A WITH (NOLOCK)
  ON C.LoginUserID=A.UserID and C.LoginUserID= @LoginUserID
  AND [Email ID]=EMail and IsRedundantEmail=”true”
===========================converting email to bigint

SELECT @UID = convert(bigint,convert(varbinary(260),reverse(convert(varbinary(260),
       CAST((CONVERT(varchar,@PartnerId))+@AccountEmail
       AS varbinary(260)) ))))