SpeedyGonzalez
New Member
Kinda stuck on this one. I have a table with name/value columns:\[code\]NameValue table-----------------------------------------------------------Name varchar(100) Value varchar(100)-----------------------------------------------------------FirstName First valueSecondName Second valueThirdName Null or Empty Stringetc... \[/code\]I'm trying to get my result to look like the following XML, but I can't quite get there.\[code\]<MyValues xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <FirstName>First value</FirstName> <SecondName>Second value</SecondName> <ThirdName xsi:nil="true" /></MyValues>\[/code\]To get the dynamic names working, I concat the XML and cast as XML like so:\[code\]select cast('<' + name + '>' + value + '</' + name + '>' as xml)from NameValuefor xml raw(''), root('MyValues'), elements xsinil\[/code\]The above query produces this XML without \[code\]xsi:nil="true"\[/code\] \[code\]<MyValues xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <FirstName>First value</FirstName> <SecondName>Second value</SecondName> <ThirdName /></MyValues>\[/code\]If I don't cast as XML, I'm left with the following:\[code\]<MyValues xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><FirstName>First value</FirstName><etc...\[/code\]I tried adding the \[code\]xsi:nil="true"\[/code\] in the concatination, but receive errors about the missing namespace. I'm guessing to make this work, I'm going to have to add the same namespace to every single row that contains null or empty string, so the result would look like the following:\[code\]<MyValues> <FirstName>First value</FirstName> <SecondName>Second value</SecondName> <ThirdName xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" /></MyValues>\[/code\]There could be a few hundred of these empty strings in the resultset, so I'd prefer to put the namespace at the root level to conserve on bandwidth. Is this at all possible?