To array or not to array

1

I am trying to convert xml to json and running into an issue where one element is appearing as an array and I don't want it to. Here's sample data and ddl.

Here are the results I am getting. You can see the array at RemoveBracketHere. I want to remove this bracket and corresponding close bracket. Any help would be appreciated. TIA

[
{
"id": 1,
"templateContent": {
"RemoveBracketHere": [
{
"userInfo": {
"userID": "DummyUserID"
},
"column": [
{
"excelColumn": "Dept",
"StdColumn": "Department Number"
},
{
"excelColumn": "Season",
"StdColumn": "Season"
},
{
"excelColumn": "Class",
"StdColumn": "Class"
}
]
}
]
}
}
]

 drop table if exists #t
 go
 
 create table #t (Division int not null primary key, x xml)
 insert into #t values (1, '<Columns>
   <UserInfo>
     <UserID>DummyUserID</UserID>
   </UserInfo>
   <Column>
     <ExcelColumn>Dept</ExcelColumn>
     <StdColumn>Department Number</StdColumn>
   </Column>
   <Column>
     <ExcelColumn>Season</ExcelColumn>
     <StdColumn>Season</StdColumn>
   </Column>
   <Column>
     <ExcelColumn>Class</ExcelColumn>
     <StdColumn>Class</StdColumn>
   </Column>
 </Columns>')
 
 
 select (
 	Select  Division as id,
 			(select x.value('(//Columns/UserInfo/UserID)[1]', 'varchar(30)')  AS 'userInfo.userID',
 			(
 			select
 				 coldata.value('(ExcelColumn)[1]', 'varchar(30)') AS 'excelColumn'
 				,coldata.value('(StdColumn)[1]', 'varchar(30)') AS 'StdColumn'
 			FROM #t d
 			cross apply x.nodes('//Columns/Column') as  xmldata(coldata)
 			where d.Division = d1.Division
 			FOR json path) as 'column'
 	from #t d1
 	where d1.Division = dim.Division
 	for json path)  'templateContent.WantSquigglyBracketNotBracketHere'
 	from #t Dim
 	for json path
 	) as json
2

:joy: @mike01 where is the xml

3

Sorry, it got left out on the cut and paste. Should be there now

4

Could you please manually modify the final json you are getting to make it look like the way you want it and then validate it here

5

I modified the expected result to be valid json. Thanks yosiasz

6

:smiley: but you still have RemoveBracketHere. What I was requesting is for you to manually generate the desired final json and then vet it against the online validator.

7

I did. This is the output I want. The brackets are removed

[
{
"id": 1,
"templateContent": {
"RemoveBracketHere":
{
"userInfo": {
"userID": "DummyUserID"
},
"column": [
{
"excelColumn": "Dept",
"StdColumn": "Department Number"
},
{
"excelColumn": "Season",
"StdColumn": "Season"
},
{
"excelColumn": "Class",
"StdColumn": "Class"
}
]
}

  }

}
]

8

Would this work?

;with flat
as
(
	Select  Division as id,
 			x.value('(//Columns/UserInfo/UserID)[1]', 'varchar(30)')  AS 'userID',
			coldata.value('(ExcelColumn)[1]', 'varchar(30)') AS 'excelColumn',
 			coldata.value('(StdColumn)[1]', 'varchar(30)') AS 'StdColumn'
	from #t d1
	cross apply x.nodes('//Columns/Column') as  xmldata(coldata)
)
select distinct p.id, 
      ui.userID as 'templateContent.details.userInfo.userID',
      (SELECT excelColumn, StdColumn 
	    FROM flat c WHERE p.id = c.id 
		 FOR JSON PATH) as 'templateContent.details.columns'
	   
  from flat p
  join (select distinct id,
 userID from flat) ui on p.id = ui.id
  for json path
9

Also would recommend you take a look at the following (OData spec)

10

yosiasz,

that did work. Thanks for the help