SQLTeam.com | Weblogs | Forums

To array or not to array

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

:joy: @mike01 where is the xml

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

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

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

: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.

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"
}
]
}

  }

}
]

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

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

yosiasz,

that did work. Thanks for the help