[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Need help to create a XML from a Select Statement

Hello EE !

I need help to generate a XML with a Select Statement.
I have created a little code that you can run it will give you the Select (with temp tables) (see snippet 1)

In snippet 2, it is the result I would like to have in the XML.  I am sure is it possible to do, I am just missing knowledge with that. Also I think It is a little nice challenge for any experts here...fun to do I think.


Note that in the "result xml"  when you see    <keyName>playName</key>   is it "hardcoded" like that, I need that line as it is.  Same thing with <keyName>categories</key>    The rest came from the Select.

Note also in my select I only have ID 1 in the @tmp1Level but should I had put also ID 2  the XMl would have another  <dict>  with another <key>playName</key> etc...
is it possible to help me ?
DECLARE @tmp1 table ( [PresentationID] int
					, [PresentationDesc] varchar(50))
								
DECLARE @tmp1Level table ( [PresentationID] int
					, [PresentationLevelID] int
					, [PresentationDescLevel] varchar(50)
					, [PresentationComments] varchar(50))

INSERT INTO @tmp1 ([PresentationID], [PresentationDesc])
VALUES (1, 'FALL 2011')
INSERT INTO @tmp1 ([PresentationID], [PresentationDesc])
VALUES (2, 'WINTER 2011')

INSERT INTO @tmp1Level ([PresentationID], [PresentationLevelID], [PresentationDescLevel], [PresentationComments])
VALUES (1, 1, 'Jackets', 'This is a Jacket')
INSERT INTO @tmp1Level ([PresentationID], [PresentationLevelID], [PresentationDescLevel], [PresentationComments])
VALUES (1, 2, 'Denim', 'This is a Denim')


SELECT  t.PresentationID
	,	t.PresentationDesc
	,	p.PresentationLevelID
	,	p.PresentationDescLevel
	,	p.presentationComments
FROM @tmp1 t
INNER JOIN @tmp1Level p on p.PresentationID = t.PresentationID

Open in new window

XML result I would like :

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<array>
    <dict>
        <keyName>playName</key>
        <string>FALL 2011</string>
        <key>categories</key>
        <array>
            <dict>
               <key>PresentationID</key>
               <integer>1</integer>
               <key>PresentationDesc</key>
               <string>FALL 2011</string>
               <key>PresentationIDLevel</key>
               <integer>1</integer>
               <string>Jackets</string>
               <key>PresentationComments</key>
               <string>This is a Jacket</string>
            </dict>
            <dict>
               <key>PresentationID</key>
               <integer>1</integer>
               <key>PresentationDesc</key>
               <string>FALL 2011</key>
               <key>PresentationIDLevel</key>
               <integer>2</integer>
               <string>Denim</string>
               <key>PresentationComments</key>
               <string>This is a Denim</string>
            </dict>
        </array>
    </dict>
</array>
</plist>

Open in new window

0
PhilippeRenaud
Asked:
PhilippeRenaud
  • 4
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hello.

Leave it to Apple / iTunes. *smile* I have not messed with the iTunes XML to verify this will ultimately work as you need it, but at least I can help with the SQL side. Attached please find a sample code that does everything shown.

NOTE: the final output is one long text string because this bit has to be concatenated at the end:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">

Open in new window


Because of this concatenation, you will note the PLIST selection does not use the keyword TYPE and causes the output to be VARCHAR versus XML to avoid incompatible types error.

There are other notes in the code, so hopefully everything else makes sense.

Best regards and happy coding,

Kevin
/* BEGIN Test Data */
DECLARE @tmp1 table ( [PresentationID] int
					, [PresentationDesc] varchar(50))
								
DECLARE @tmp1Level table ( [PresentationID] int
					, [PresentationLevelID] int
					, [PresentationDescLevel] varchar(50)
					, [PresentationComments] varchar(50))

INSERT INTO @tmp1 ([PresentationID], [PresentationDesc])
VALUES (1, 'FALL 2011')
INSERT INTO @tmp1 ([PresentationID], [PresentationDesc])
VALUES (2, 'WINTER 2011')

INSERT INTO @tmp1Level ([PresentationID], [PresentationLevelID], [PresentationDescLevel], [PresentationComments])
VALUES (1, 1, 'Jackets', 'This is a Jacket')
INSERT INTO @tmp1Level ([PresentationID], [PresentationLevelID], [PresentationDescLevel], [PresentationComments])
VALUES (1, 2, 'Denim', 'This is a Denim')
/* END Test Data */

/*
<!-- Desired Results -->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<array>
    <dict>
        <keyName>playName</keyName>
        <string>FALL 2011</string>
        <key>categories</key>
        <array>
            <dict>
               <key>PresentationID</key>
               <integer>1</integer>
               <key>PresentationDesc</key>
               <string>FALL 2011</string>
               <key>PresentationIDLevel</key>
               <integer>1</integer>
               <string>Jackets</string>
               <key>PresentationComments</key>
               <string>This is a Jacket</string>
            </dict>
            <dict>
               <key>PresentationID</key>
               <integer>1</integer>
               <key>PresentationDesc</key>
               <string>FALL 2011</string>
               <key>PresentationIDLevel</key>
               <integer>2</integer>
               <string>Denim</string>
               <key>PresentationComments</key>
               <string>This is a Denim</string>
            </dict>
        </array>
    </dict>
</array>
</plist>
*/
SELECT /* Add literal XML and DOCTYPE directives. */
'<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">' +
/* PLIST */
(SELECT '1.0' AS "@version", (
/* Build outer array from @tmp1 */
SELECT 'playName' AS "keyName", t.PresentationDesc AS "string", 'categories' AS "key"
     /* Build inner array from @tmp1Level */
     , (SELECT 'PresentationID' AS "key", t.PresentationID as "integer"
	         , 'PresentationDesc' AS "key", t.PresentationDesc as "string"
	         , 'PresentationIDLevel' AS "key", p.PresentationLevelID as "integer"
	         , /*'PresentationDescLevel' AS "key",*/ p.PresentationDescLevel as "string"
	         , 'presentationComments' AS "key", p.presentationComments as "string"
        FROM @tmp1Level p 
		WHERE p.PresentationID = t.PresentationID
		FOR XML PATH('dict'), TYPE, ROOT('array'))
FROM @tmp1 t
/* Add WHERE clause to exlude @tmp1 records that have no @tmp1Level matches if needed. */
FOR XML PATH('dict'), TYPE, ROOT('array'))
FOR XML PATH('plist'))
;

Open in new window

0
 
PhilippeRenaudAuthor Commented:
Wow Thanks, would it be hard not to have it has a Full long text string? (To see if everyting is fine?)
It looks fine but lets say I wanted to test with WINTER 2011 also


if iits not possible I can try to put space myself after running the query..
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry, I started to make that point when I talking about manually adding the <?xml ...> and <!DOCTYPE ...> and forgot to complete the thought. Just comment out the top portion (and the final parenthesis):
--SELECT /* Add literal XML and DOCTYPE directives. */
--'<?xml version="1.0" encoding="UTF-8"?>
--<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">' +
--/* PLIST */
--(
SELECT '1.0' AS "@version", (
/* Build outer array from @tmp1 */
SELECT 'playName' AS "keyName", t.PresentationDesc AS "string", 'categories' AS "key"
     /* Build inner array from @tmp1Level */
     , (SELECT 'PresentationID' AS "key", t.PresentationID as "integer"
	         , 'PresentationDesc' AS "key", t.PresentationDesc as "string"
	         , 'PresentationIDLevel' AS "key", p.PresentationLevelID as "integer"
	         , /*'PresentationDescLevel' AS "key",*/ p.PresentationDescLevel as "string"
	         , 'presentationComments' AS "key", p.presentationComments as "string"
        FROM @tmp1Level p 
		WHERE p.PresentationID = t.PresentationID
		FOR XML PATH('dict'), TYPE, ROOT('array'))
FROM @tmp1 t
/* Add WHERE clause to exlude @tmp1 records that have no @tmp1Level matches if needed. */
FOR XML PATH('dict'), TYPE, ROOT('array'))
FOR XML PATH('plist')
--)
;

Open in new window


You should get XML automatically. If not, add TYPE to the last FOR XML. Output with sample data looks like this after clicking on the value in SSMS:
<plist version="1.0">
  <array>
    <dict>
      <keyName>playName</keyName>
      <string>FALL 2011</string>
      <key>categories</key>
      <array>
        <dict>
          <key>PresentationID</key>
          <integer>1</integer>
          <key>PresentationDesc</key>
          <string>FALL 2011</string>
          <key>PresentationIDLevel</key>
          <integer>1</integer>
          <string>Jackets</string>
          <key>presentationComments</key>
          <string>This is a Jacket</string>
        </dict>
        <dict>
          <key>PresentationID</key>
          <integer>1</integer>
          <key>PresentationDesc</key>
          <string>FALL 2011</string>
          <key>PresentationIDLevel</key>
          <integer>2</integer>
          <string>Denim</string>
          <key>presentationComments</key>
          <string>This is a Denim</string>
        </dict>
      </array>
    </dict>
    <dict>
      <keyName>playName</keyName>
      <string>WINTER 2011</string>
      <key>categories</key>
    </dict>
  </array>
</plist>

Open in new window


Note how WINTER 2011 shows up even though it does not have matching details -- that is what my comment about the WHERE clause is referring to.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
PhilippeRenaudAuthor Commented:
Yea ok so I would do a WHERE kind of huh...

WHERE p.PresentationID  = t.PresentationID  something like that I guess
0
 
Kevin CrossChief Technology OfficerCommented:
You mean to check for matches? I would probably go for EXISTS in this scenario, but if performance issues arise maybe duplicate your original JOIN with GROUP BY in outer query to get one node per category. The EXISTS would look something like this:

--SELECT /* Add literal XML and DOCTYPE directives. */
--'<?xml version="1.0" encoding="UTF-8"?>
--<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">' +
--/* PLIST */
--(
SELECT '1.0' AS "@version", (
/* Build outer array from @tmp1 */
SELECT 'playName' AS "keyName", t.PresentationDesc AS "string", 'categories' AS "key"
     /* Build inner array from @tmp1Level */
     , (SELECT 'PresentationID' AS "key", t.PresentationID as "integer"
	         , 'PresentationDesc' AS "key", t.PresentationDesc as "string"
	         , 'PresentationIDLevel' AS "key", p.PresentationLevelID as "integer"
	         , /*'PresentationDescLevel' AS "key",*/ p.PresentationDescLevel as "string"
	         , 'presentationComments' AS "key", p.presentationComments as "string"
        FROM @tmp1Level p 
		WHERE p.PresentationID = t.PresentationID
		FOR XML PATH('dict'), TYPE, ROOT('array'))
FROM @tmp1 t
/* Filter if no matches in @tmp1Level. */
WHERE EXISTS (
   SELECT 1
   FROM @tmp1Level p 
   WHERE p.PresentationID = t.PresentationID
)
FOR XML PATH('dict'), TYPE, ROOT('array'))
FOR XML PATH('plist')
--)
;

Open in new window

0
 
PhilippeRenaudAuthor Commented:
many many thanks.
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome!
Best regards and happy coding,

Kevin
0
 
PhilippeRenaudAuthor Commented:
Hi Visa, any idea on that question I have created ? It is regarding your string result you gave me in the select


http://www.experts-exchange.com/Apple/Programming/Q_27400423.html

thanks!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now