XML Path question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML Path question

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-07-14 : 02:03:20

Hi, I need to build XML from the table data but not able to figure out how to go about this. Below is the sample data , the desired output and the query I am using. Can anyone let me know the way to do this.

-- SAMPLE DATA
DECLARE @TMP TABLE (ID INT,NAME VARCHAR(50),VALUE VARCHAR(50))
INSERT INTO @TMP
SELECT 1,'A',10
UNION
SELECT 1,'B',20
UNION
SELECT 1,'C',30
UNION
SELECT 1,'D',40
UNION
SELECT 1,'E',NULL
UNION
SELECT 1,'F',NULL

--DESIRED OUTPUT
<Results>
<Result>
<DATA>FormID</DATA> -- THIS NEEDS TO BE HARD-CODED AS 'FORMID'
<DATA>A</DATA>
<DATA>B</DATA>
<DATA>C</DATA>
<DATA>D</DATA>
<DATA>E</DATA>
<DATA>F</DATA>
</Result>
<Result>
<DATA>1</DATA>
<DATA>10</DATA>
<DATA>20</DATA>
<DATA>30</DATA>
<DATA>40</DATA>
<DATA>NULL</DATA> -- CAN THIS BE NULL AS NO DATA FOR E
<DATA>NULL</DATA>
</Result>
</Results>

--My Query, NEED TO MODIFY TO GET DESIRED OUTPUT
SELECT
(
SELECT NAME AS DATA from @TMP FOR XML PATH(''),ROOT('Result'),TYPE
),
(
SELECT VALUE AS DATA from @TMP FOR XML PATH(''),ELEMENTS XSINIL,ROOT('Result'),TYPE
)
FROM @TMP FOR XML PATH(''),ROOT('Results')

Thanks

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-07-14 : 04:02:34

Solved it:

SELECT
(
SELECT 'FORMID' AS 'Data',(SELECT NAME AS DATA from @TMP FOR XML PATH(''),TYPE,ELEMENTS)
from @TMP
GROUP BY ID
FOR XML PATH('Result'),TYPE
),
(SELECT ID AS 'Data',(SELECT VALUE AS DATA from @TMP FOR XML PATH(''),TYPE,ELEMENTS XSINIL)
from @TMP
GROUP BY ID
FOR XML PATH('Result'),TYPE
)
FROM @TMP
GROUP BY ID
FOR XML PATH(''),ROOT('Results'),TYPE

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-07-14 : 23:46:07

Another question, please see the comments on the query:

-- SAMPLE DATA
DECLARE @TMP TABLE (ID INT,NAME VARCHAR(50),VALUE VARCHAR(50))
INSERT INTO @TMP
SELECT 1,'A','10'
UNION
SELECT 1,'D',''
UNION
SELECT 1,'E',NULL

-- SHOWS NULL/EMPTY VALUES AS <DATA />
SELECT
(
SELECT 'FORMID' AS 'Data',(SELECT NAME AS DATA from @TMP FOR XML PATH(''),TYPE,ELEMENTS)
from @TMP
GROUP BY ID
FOR XML PATH('Result'),TYPE
),
(SELECT ID AS 'Data',(SELECT ISNULL(VALUE,'') AS DATA from @TMP FOR XML PATH(''),TYPE,ELEMENTS)
from @TMP
GROUP BY ID
FOR XML PATH('Result'),TYPE
)
FROM @TMP
GROUP BY ID
FOR XML PATH(''),ROOT('Results'),TYPE

-- SHOWS NULL/EMPTY VALUES AS <DATA></DATA> AS AGAINST ABOVE <DATA />. WHY IS THIS?
SELECT ISNULL(VALUE,'') AS DATA from @TMP FOR XML PATH('')

Can someone let me know how XML decides to build <DATA></DATA> or <DATA /> for NULL/blank values.
Also I read here:
http://beyondrelational.com/blogs/jacob/archive/2008/08/21/for-xml-path-generating-an-element-having-null-value.aspx
how to handle NULLS. How does this differ from using ISNULL as used above?

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources