All geek questions in one place

TSQL for XML attribute PATH is enabled, type

Basically, I need to output both a single value and the value of the muti field from the database. Most data users are not technical, and it would be nice if they could read XML and understand it. Using another post, I got almost all the ways.

The problem is that the element name cannot contain spaces. You must include a display name, which may have spaces. For example MVtext displayName = "Multi Value Text". If the correct answer is an inline circuit or other approach, please let me know. This is not how I can publish a static schema, because the application can dynamically add fields. And the fields can change with the type of report. The fields are in a table, and I read this table to build the selection.

How can I tell a display name that may contain spaces for elements? SQL 2008 R2.

SELECT top 4 [sv].[sID] AS '@sID' ,[sv].[sParID] AS '@sParID' ,[sv].[docID] AS 'docID' ,[sv].addDate as 'addDate' ,(SELECT [value] AS 'value' FROM [docMVtext] as [mv] WHERE [mv].[sID] = [sv].[sID] AND [mv].[fieldID] = '113' ORDER BY [mv].[value] FOR XML PATH (''), type ) AS "To" ,(SELECT [value] AS 'value' FROM [docMVtext] as [mv] WHERE [mv].[sID] = [sv].[sID] AND [mv].[fieldID] = '130' ORDER BY [mv].[value] FOR XML PATH (''), type ) AS "MVtest" FROM [docSVsys] as [sv] WHERE [sv].[sID] >= '57' ORDER BY [sv].[sParID], [sv].[sID] FOR XML PATH('Document'), root('Documents')

It produces:

 <Documents> <Document sID="57" sParID="57"> <docID>3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA</docID> <addDate>2011-10-28T12:26:00</addDate> <To> <value>Frank Ermis</value> <value>Keith Holst</value> <value>Mike Grigsby</value> </To> <MVtest> <value>MV test 01</value> <value>MV test 02</value> <value>MV test 03</value> <value>MV test 04</value> </MVtest> </Document> <Document sID="58" sParID="57"> <docID>3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA.1</docID> <addDate>2011-10-28T12:26:00</addDate> </Document> <Document sID="59" sParID="59"> <docID>3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA</docID> <addDate>2011-10-28T12:26:00</addDate> <To> <value>Vladimir Gorny</value> </To> </Document> <Document sID="60" sParID="59"> <docID>3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA.1</docID> <addDate>2011-10-28T12:26:00</addDate> </Document> </Documents>

I'm tired

  SELECT [value] AS 'value', 'MV test' as 'dispName'

But I get

  <MVtest> <value>MV test 01</value> <dispName>MV test</dispName> <value>MV test 02</value> <dispName>MV test</dispName> <value>MV test 03</value> <dispName>MV test</dispName> <value>MV test 04</value> <dispName>MV test</dispName> </MVtest>

SELECT [value] AS 'value', 'MV test' as '@dispName' Throws a runtime error Missing string tag (empty string tag name) cannot be used with XML format serialization using attributes

Required Conclusion:

  <Documents> <Document sID="57" sParID="57"> <docID>3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA</docID> <addDate>2011-10-28T12:26:00</addDate> <To> <value>Frank Ermis</value> <value>Keith Holst</value> <value>Mike Grigsby</value> </To> <MVtest dispName="Multi Value Text"> <value>MV test 01</value> <value>MV test 02</value> <value>MV test 03</value> <value>MV test 04</value> </MVtest> </Document> </Documents>

Decision:

 SELECT top 4 [sv].[sID] AS '@sID' ,[sv].[sParID] AS '@sParID' ,'SV' as 'docID/@SVMV' ,[sv].[docID] AS 'docID' ,'SV' as 'addDate/@SVMV' ,[sv].addDate as 'addDate' ,'Email To' as 'To/@DisplayName' ,'MV' as 'To/@SVMV' ,(SELECT [value] AS 'value' FROM [docMVtext] as [mv] WHERE [mv].[sID] = [sv].[sID] AND [mv].[fieldID] = '113' ORDER BY [mv].[value] FOR XML PATH (''), type ) AS "To" ,'Multi Value Text Sample' as 'MVtext130/@DisplayName' ,'MV' as 'MVtext130/@SVMV' ,(SELECT [value] AS 'value' FROM [docMVtext] as [mv] WHERE [mv].[sID] = [sv].[sID] AND [mv].[fieldID] = '130' ORDER BY [mv].[value] FOR XML PATH (''), type ) AS "MVtext130" FROM [docSVsys] as [sv] WHERE [sv].[sID] >= '57' ORDER BY [sv].[sParID], [sv].[sID] FOR XML PATH('Document'), root('Documents')

Solution output:

 <Documents> <Document sID="57" sParID="57"> <docID SVMV="SV">3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA</docID> <addDate SVMV="SV">2011-10-28T12:26:00</addDate> <To DisplayName="Email To" SVMV="MV"> <value>Frank Ermis</value> <value>Keith Holst</value> <value>Mike Grigsby</value> </To> <MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV"> <value>MV test 01</value> <value>MV test 02</value> <value>MV test 03</value> <value>MV test 04</value> </MVtext130> </Document> <Document sID="58" sParID="57"> <docID SVMV="SV">3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA.1</docID> <addDate SVMV="SV">2011-10-28T12:26:00</addDate> <To DisplayName="Email To" SVMV="MV" /> <MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV" /> </Document> <Document sID="59" sParID="59"> <docID SVMV="SV">3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA</docID> <addDate SVMV="SV">2011-10-28T12:26:00</addDate> <To DisplayName="Email To" SVMV="MV"> <value>Vladimir Gorny</value> </To> <MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV" /> </Document> <Document sID="60" sParID="59"> <docID SVMV="SV">3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA.1</docID> <addDate SVMV="SV">2011-10-28T12:26:00</addDate> <To DisplayName="Email To" SVMV="MV" /> <MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV" /> </Document> </Documents>

This is a great solution to my question as asked. One of the symptoms of adding an attribute is that I get the element, even if there are zero rows of values. Ideally, it will not list an element if there are no values.

I tried the Case statement, but even if I set the value to '', it will display the element.

  ,[MVtext130/@DisplayName] = Case (select COUNT(*) FROM [docMVtext] WHERE [docMVtext].[sID] = [sv].[sID] AND [docMVtext].[fieldID] = '130') when '0' then '' else 'Multi Value Text Sample' end

Updated solution for item without list without values:

  SELECT top 4 [sv].[sID] AS '@sID' ,[sv].[sParID] AS '@sParID' ,'SV' as 'docID/@SVMV' ,[sv].[docID] AS 'docID' ,'SV' as 'addDate/@SVMV' ,[sv].addDate as 'addDate' ,(select top(1) 'Email To' from [docMVtext] as C where C.[sID] = [sv].[sID] and c.fieldID = '113' ) as 'To/@DisplayName' ,(select top(1) 'MV' from [docMVtext] as C where C.[sID] = [sv].[sID] and c.fieldID = '113' ) as 'To/@SVMV' --,'Email To' as 'To/@DisplayName' --,'MV' as 'To/@SVMV' ,(SELECT [value] AS 'value' FROM [docMVtext] as [mv] WHERE [mv].[sID] = [sv].[sID] AND [mv].[fieldID] = '113' ORDER BY [mv].[value] FOR XML PATH (''), type ) AS "To" ,(select top(1) 'Multi Value Text Sample' from [docMVtext] as C where C.[sID] = [sv].[sID] and c.fieldID = '130' ) as 'MVtext130/@DisplayName' ,(select top(1) 'MV' from [docMVtext] as C where C.[sID] = [sv].[sID] and c.fieldID = '130' ) as 'MVtext130/@SVMV' --,'Multi Value Text Sample' as 'MVtext130/@DisplayName' --,'MV' as 'MVtext130/@SVMV' ,(SELECT [value] AS 'value' FROM [docMVtext] as [mv] WHERE [mv].[sID] = [sv].[sID] AND [mv].[fieldID] = '130' ORDER BY [mv].[value] FOR XML PATH (''), type ) AS "MVtext130" FROM [docSVsys] as [sv] WHERE [sv].[sID] >= '57' ORDER BY [sv].[sParID], [sv].[sID] FOR XML PATH('Document'), root('Documents')
+4
source share
1 answer

I'm not sure that I understand what you want, and it's hard to do something with your code without the tables you have, so I created a sample that, I believe, does what you already have.

Table and installation data:

 declare @Main table ( MainID int identity, Value int ) declare @Child table ( ChildID int identity, MainID int, Value int ) insert into @Main values (10),(20),(30) insert into @Child values (1,100),(2,200),(2,210)

A request that does about the same as yours already does.

 select M.MainID as '@MainID', M.Value as 'MainValue', (select C.Value as ChildValue from @Child as C where C.MainID = M.MainID for xml path(''), type) as Child from @Main as M for xml path('Document'), root('Documents')

Result:

 <Documents> <Document MainID="1"> <MainValue>10</MainValue> <Child> <ChildValue>100</ChildValue> </Child> </Document> <Document MainID="2"> <MainValue>20</MainValue> <Child> <ChildValue>200</ChildValue> <ChildValue>210</ChildValue> </Child> </Document> <Document MainID="3"> <MainValue>30</MainValue> </Document> </Documents>

I believe your expected result would be like this, with the display name attribute on the child nodes.

 <Documents> <Document MainID="1"> <MainValue>10</MainValue> <Child DisplayName="Child Display Name"> <ChildValue>100</ChildValue> </Child> </Document> <Document MainID="2"> <MainValue>20</MainValue> <Child DisplayName="Child Display Name"> <ChildValue>200</ChildValue> <ChildValue>210</ChildValue> </Child> </Document> <Document MainID="3"> <MainValue>30</MainValue> </Document> </Documents>

So that you use this query instead:

 select M.MainID as '@MainID', M.Value as 'MainValue', (select top(1) 'Child Display Name' from @Child as C where C.MainID = M.MainID) as 'Child/@DisplayName', (select C.Value as ChildValue from @Child as C where C.MainID = M.MainID for xml path(''), type) as Child from @Main as M for xml path('Document'), root('Documents')
+12
source

Source: https://habr.com/ru/post/1386149/

More articles:


All Articles