SQL Server 2014 JSON into table

See also questions close to this topic

• How to convert String having key=value pairs to Json

  myString = {AcquirerName=abc, AcquiringBankCode=0.2, ApprovalCode=00};

  I want to convert it to the following string.

  {"AcquirerName": "abc", "AcquiringBankCode": 0.2, "ApprovalCode": 0};
  

  How can I do it in java?
• Fetch doesn't retrive data

  I am currently building a login form which will be using a restAPI for the registration/Auth but I'm receiving a 401 error when using fetch to test the retrieved data.

  console.log(fetch('http://localhost:5000/users/'), { method: 'get', headers: { 'Content-Type': 'application/json' }, })

  .then(res => res.json())
  .then(json => console.log(json))</code>
  
• NextJS: Error serializing `.data.data` returned from `getServerSideProps`

  I'm new to NextJS. I'm working on a simple page in which I need to retrieve data from my backend app. My backend app is a totally separate app written in go. My undestanding is that I must use getServerSideProps to get data from the server on load, so I've got the following:

  myProject/pages/category/new.js:

  export default function AddNewCategory(data) {
  ...
  }
  
  export const getServerSideProps = async () => {
      const data = await getAllCategories();
      console.log(await data)
      return {
          props: {
              data: { data }
          }
      };
  };
  

  myProject/api/category.js:

  import axios from "axios";
  
  // getAllCategories returns a list of all active categories
  export const getAllCategories = async() => {
  
      axios.get("http://127.0.0.1:8080/api/v1/categories")
          .then(function (response) {
              console.log(response.data)
              return response.data;
          })
          .catch(error => {
              console.error("error")
              console.log(error)
          })
  
  }
  

  As you can see I've got a print statement in getAllCategories which prints:

  {
    data: [
      {
        id: 1,
        name: 'Pop',
        slug: 'pop',
        description: 'Pop',
        parent: 0,
        active: true,
        created_at: '2022-05-03T19:50:00-04:00',
        updated_at: '2022-05-03T19:50:00-04:00',
        deleted_at: null
      },
      {
        id: 3,
        name: 'Pop 2',
        slug: 'pop-2',
        description: 'Pop',
        parent: 0,
        active: true,
        created_at: '2022-05-03T19:50:24-04:00',
        updated_at: '2022-05-03T19:50:24-04:00',
        deleted_at: null
      }
    ]
  }
  

  yet I'm getting the following error:

  error - Error: Error serializing .data.data returned from getServerSideProps in "/category/new". Reason: undefined cannot be serialized as JSON. Please use null or omit this value.

  I saw around that I should try to convert the data to string and then back to json:

  return { 
    props: { 
      data: JSON.parse(JSON.stringify(data)) 
    } 
  };
  

  but when I do this I'm getting a different error:

  error - SyntaxError: Unexpected token u in JSON at position 0

  I'm using next@12.1.5

  Any idea what's going on?
• Tables won't appear as selectable object after creating it

  SELECT *
  FROM EmployeeDemographics
  
  
  Msg 208, Level 16, State 1, Line 2
  
  Invalid object name 'EmployeeDemographics'.
  

  I'm currenlty watching Alex the Analyst's SQL tutorial and everytime I create a table, close, and restart SQL, the table is not found by SQL. When I use a statement such as FROM the table doesn't appear in the list of selectable objects. When I type it manually, the table_name has red lines under it indicating that it's an invalid object name. When I run the code I get the error message above. I tried refreshing the database and the Intellisense to no avail. Let me know if I should post the code for the table and thanks in advanced.
• count how many people who buy the same item at second time and how many people who did not buy the second item at second time (compare to firsttime)

  Here is the question:count how many people who buy the same item at second time and how many people who did not buy the second item at second time (compare to buying_order = 1)

  We have a table:

  id	item	date	buying_order
  1	1	19990101	1
  1	1	19990102	2
  2	1	19990102	1
  2	2	19990101	2
  2	3	19990103	1

  To be more clear, if id 1 buy item 1 on 19990101, that is the first purchased item so the buying_order is 1. Our concern is know how many people buy a specific item at their first time, and how many people re-purchase that specific item at the second time.

  Here are the logic I have tried, but not work

  SELECT a.id FROM (SELECT id FROM table WHERE buying_order = 1) AS a 
  LEFT JOIN table AS b ON a.id = b.id 
  WHERE b.buying_order=2 and a.item = b.item
  

  Expected result:

  item	first_purchase_customer	second_purchase	second_buy_other_item_count
  1	2	1	1

  For item 1, there are two first purchase at order 1, and there is only one customer buy item 1 at order 2.

  NOTE: The order can be higher than 2, such that order = 3,4,..., but we only care the people who buy or not buy the same item at their second purchase.
• Total up transactions

  I am using SQL Server Management Studio v18.

  I have an original transaction that might get adjusted either by amount or by commission%.

  I want a query to total up the transactions based on ID and company and I want to keep the original transaction's TransType.

  I have full understanding on how to aggregate columns but I don't know how to keep the TransType.

  Original table:

  ID	TransType	Amount	Commission	Commission%	Company	Notes
  555	RE	500	125	25%	Company A	Original Transaction
  555	RT	-400	-100	0%	Company A	Adjustment
  666	RA	500	125	25%	Company A	Original Transaction
  666	AD	0	-100	-20%	Company A	Adjustment
  666	RN	200	40	20%	Company B	Original Transaction

  Final result:

  ID	TransType	Amount	Commission	Commission%	Company
  555	RE	100	25	25%	Company A
  666	RA	500	25	5%	Company A
  666	RN	200	40	20%	Company B
• Java - Update Elements in Large XML Files

  I work with very large XML datasets (1 GB+) and need to backtrack and update specific elements per node, depending on the values of other elements that follow.

  For example, in this record/node:

  <user>
      <role>Associate</role>
      <team>Hufflepuff</team>
      <experience>7</experience>
  </user>
  
  

  Since "experience" is greater than 5 years, the role needs to be updated from "Associate" to "Senior."

  I would like to avoid loading the entire file into memory via the DOM.

  Ideally, I would process each single "user" in the XML and append the data to a new XML file one at a time. I started off by processing in a stream using StAX, but I don't know how to transform each XMLEventWriter event content into a useable DOM document that writes to an XML file and clears from memory afterwards.

  If the description is unclear in any way, please let me know. Any help on this will be greatly appreciated.

  Thanks.
• cant read field in xml section

  Using python I got to the correct iteration of the XML (forecast) section, but one child field I cant seem to be able to read here is the section from the XML

  <forecast>
  <fcst_time_from>2022-05-04T16:00:00Z</fcst_time_from>
  <fcst_time_to>2022-05-04T20:00:00Z</fcst_time_to>
  <change_indicator>FM</change_indicator>
  <wind_dir_degrees>110</wind_dir_degrees>
  <wind_speed_kt>6</wind_speed_kt>
  <visibility_statute_mi>6.21</visibility_statute_mi>
  <sky_condition sky_cover="SCT" cloud_base_ft_agl="4500"/>
  <sky_condition sky_cover="SCT" cloud_base_ft_agl="25000"/>
  </forecast>
  

  I can get every field except <sky_condition sky_cover="SCT" cloud_base_ft_agl="25000"/>

  here is the code where I pull the fields

  if startTime_new <= Cur_Date_UTC <= EndTime_new:
                  #Cil2 = (l.find('sky_cover')).text
                  wDir = (l.find('wind_dir_degrees')).text
                  wSpd = (l.find('wind_speed_kt')).text
                  vis = (l.find('visibility_statute_mi')).text
                  Cil = (l.find('sky_condition')).text
                  print(wDir)
                  print(wSpd)
                  print(vis)
                  print(Cil)
                  print(l)
  

  The answer given did work in a comand window but I am using beautifulsoup to get the XML . I tried to import from datetime import datetime however Cil = [x.get('sky_cover') for x in elt.findall('sky_condition')] did not work

  here is my full code

  from bs4 import BeautifulSoup as bs
  import requests
  import pytz
  from datetime import datetime
  
  #pirip=False
  
  def SetDateTimes():
      utc_time = datetime.now(pytz.utc)
      Cur_Date_UTC = utc_time.strftime("%d/%m/%y %H:%M:%S")
      return Cur_Date_UTC
  
  def FixDateTime(workingTime):
      workingTime = workingTime.replace('T', ' ')
      workingTime = workingTime.replace('Z', '')
      format = "%Y-%m-%d %H:%M:%S"
      dt_object = datetime.strptime(workingTime, format)
      day_st = dt_object.strftime("%d")
      month_st = dt_object.strftime("%m")
      year_st = dt_object.strftime("%y")
      time_st = dt_object.strftime("%H:%M:%S")
      workingTime = day_st + '/' + month_st + '/' + year_st + ' ' + time_st
      return workingTime
  
  def GetTAF3():
      USER_AGENT = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36"
      url = 'https://www.aviationweather.gov/adds/dataserver_current/httpparam?dataSource=tafs&requestType=retrieve&format=xml&hoursBeforeNow=3&timeType=issue&mostRecent=true&stationString=KEWR'
      session = requests.Session()
      session.headers['User-Agent'] = USER_AGENT
      html = session.get(url)
      soup = bs(html.text, 'html.parser')
      taf = soup.find_all("forecast")
      for l in soup.findAll('forecast'):
              startTime = l.find('fcst_time_from').text
              EndTime = (l.find('fcst_time_to')).text
              
              #print(startTime)
              startTime_new = FixDateTime(startTime)
              #print(startTime_new)
              
              #print(EndTime)
              EndTime_new = FixDateTime(EndTime)
              #print(EndTime_new)
              #break
             
              if startTime_new <= Cur_Date_UTC <= EndTime_new:
                  #Cil2 = (l.find('sky_cover')).text
  
                  
                  print(l)
                 
                  wDir = (l.find('wind_dir_degrees')).text
                  wSpd = (l.find('wind_speed_kt')).text
                  vis = (l.find('visibility_statute_mi')).text
                  Cil = (l.find('sky_condition'))
                  
                  
                  print(wDir)
                  print(wSpd)
                  print(vis)
                  print(Cil)
                  print(l)
  
                  if 'OVC' in Cil:
                      print("OVC Found")
                      pirip = True
                  else:
                      print('No PIRIP')
  
                  if float(vis) < 5:
                      print("pirip for vis")
                      pirip = True
                  else:
                      print('No PIRIP')
  
  
                          
  
  Cur_Date_UTC = SetDateTimes()   
  GetTAF3()
  #print(pirip)
  
• How can I use Continue in a choose statement in xslt

  I want to continue to check for other if statements if the first one is met. From what I read, Choose statement does not have this functionality like other languages do. One post suggested to just run multiple if statements but this is not working. After the first if statement is met it stops there, I want it to keep running.

  How can I achieve this? This is my code:

  <?xml version="1.0" encoding="UTF-8" ?>
  <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output indent="yes" omit-xml-declaration="yes"/>
      <xsl:template match="root"> 
      { 
          
          "entityList": 
          [
              <xsl:for-each select="//entityList"> 
              {
                  "notes":"<xsl:call-template name="replace">
                  <xsl:with-param name="text" select="./Notes"/>
              </xsl:call-template>",
              } <xsl:if test="position() != last()">,</xsl:if>
          </xsl:for-each> 
          ] 
      } 
      </xsl:template>
      <xsl:template name="replace">
          <xsl:param name="text"/>
          <xsl:param name="searchString">"</xsl:param>
          <xsl:param name="replaceString">\"</xsl:param>
          <xsl:param name="searchStringSpace">&#x9;</xsl:param>
          <xsl:param name="searchStringBackslash">\</xsl:param>
          <xsl:if test="contains($text,$searchString)">
            <xsl:value-of select="replace(./Notes,'&quot;','\\&quot;')"/>
          </xsl:if>
          <xsl:if test="contains($text,$searchStringSpace)">
            <xsl:value-of select="replace(./Notes,'&#x9;','\\t')"/> 
          </xsl:if>
      </xsl:template>
  </xsl:stylesheet>

  <root>
      <totalRecords>429</totalRecords>
      <offset>0</offset>
      <entityList>
          <Notes>Dear Deans, Chairs and &quot;Directors,There was a &#x9;ANDOR question about scheduling Mac Hall Auditorium,for regular classes and policy/procedure around it.,Mac Hall Auditorium was intended to be available as a,regular classroom. That is why the seats have built-in mini,desks.,Your programs are welcome to schedule classes in,Mac Auditorium  - you can request it when submitting your,course schedules for the Semester or through the Schedule,Change Request Form.,There are, however, some conditions:,1)faculty members who teach in Mac Auditorium should,anticipate that their class may be displaced if the,University-wide function (president?s address, a conference,,etc.) is scheduled at the time of their class. It would be,up to the faculty to either reschedule that,class meeting for a different time, find another room/space,or substitute the class meeting with an alternative activity,There is no easy way to inform faculty about the upcoming,events, so they would have to watch University Calendar to,determine when their classes might be affected.,2)Mac Hall will be unavailable for scheduling regular,courses during evening hours (6-10 pm) ? that time will be,set aside for Pegasus Players practices.,Natalia F. Blank, Ph.D. 2/17/21</Notes>
          
      </entityList>
      <totalPages>1</totalPages>
      <page>0</page>
      <status>success</status>
  </root>
• Procedure to display the Employees of a specific Department

  Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

  Name of the argument as Input : DeptNo

  Write the code to create the procedure

  Schema

  I wrote the Query like this but i didnt get expected output

  CREATE  PROCEDURE Employeesdept(@Deptno varchar)
  AS
  Begin
  SELECT lastname as name
  from employee
  where workdept= 'D21'
  end
  

  Expected output:

  Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------
• How to select distinct value

  I need to select all supervisor records. Our HR employee table set up like below

  Firstname	Lastname	Email	Supervisor
  Frank	Johns	fjohns	Taylor, Don
  Pat	Hope	phope	Taylor, Don
  Jen	Dow	jdow	Taylor, Don
  Taylor	Don	tdon	Olson, Mike
  Kim	Ronda	kronda	Olson, Mike
  Rob	Smith	rsmith	Olson, Mike
  Mike	Olson	molson	null

  The final result should be like this

  Firstname	Lastname	Email	Supervisor
  Taylor	Don	tdon	Olson, Mike
  Mike	Olson	molson	null

  If I use Distinct on supervisor then it gives me a list of supervisor but not their info. Please help
• SQL Join tables - join with last four digits of columns

  I'm trying to join two tables in SQL. I want to join on the column with the last four digits of a phone number with the a column from a second column where only the last four digits are shown.

  From ExcelSheet1 E
  Join Walkers W
  ON W.Lastname = E.LNAME
  AND W.Firstname = E.FNAME
  and W.PhoneNum like '%'+E.MEMBER_NUM