CODEWITHSUNDEEP
sql-serverxmlsql-server-2008xsd
Vikas Pawar
Vikas Pawar

Reputation: 135

custom xml and sql server

I have created xml from sql server 2008. My Current xml is generated using "FOR XML AUTO"

<Customer Name="john" City="Mumbai">
  <Project Project_Name="pqr" />
</Customer>
<Customer Name="Rocky" City="Delhi">
  <Project Project_Name="abc" />
  <Project Project_Name="lmn" />
</Customer>

But I want the output like

<Customer >
   <name>John</name>
   <city>Mumbai</city>  
   <Projects>
      <project>
         <Project_Name>pqr</Project_Name>
      </project>
   </Projects>
</Customer>
<Customer >
   <name>Rocky</name>
   <city>Delhi</city>  
   <Projects>
      <project>
         <Project_Name>abc</Project_Name>
        <Project_Name>lmn</Project_Name>
      </project>
   </Projects>
</Customer>

So basically i want to convert attributes of the parent element to sub-elements. And want additional customize elements. Please help me.

Thanks in Advance.

Upvotes: 1

Views: 2487

Answers (3)

wqw
wqw

Reputation: 11991

This is the more verbose FOR XML EXPLICIT type

CREATE TABLE #Customers (
            ID              INT
            , Name          VARCHAR(100)
            , City          VARCHAR(100)
            )
CREATE TABLE #Projects (
            ID              INT
            , Name          VARCHAR(100)
            , Customer_ID   INT
            )

INSERT      #Customers
SELECT      1, 'john', 'Mumbai' UNION ALL
SELECT      2, 'Rocky', 'Delhi' UNION ALL
SELECT      3, 'Stan', 'New York' --UNION ALL

INSERT      #Projects
SELECT      1, 'pqr', 1 UNION ALL
SELECT      2, 'abc', 2 UNION ALL
SELECT      3, 'lmn', 2

SELECT      h.Tag
            , h.Parent
            , NULL          AS [Customer!1]
            , c.Name        AS [Name!1000]
            , c.City        AS [City!2000]
            , NULL          AS [Projects!3000]
            , NULL          AS [Project!3100]
            , p.Name        AS [Project_Name!3110]
FROM        (
            SELECT      NULL, 1 UNION ALL
            SELECT          1, 1000 UNION ALL
            SELECT          1, 2000 UNION ALL
            SELECT          1, 3000 UNION ALL
            SELECT              3000, 3100 UNION ALL
            SELECT                  3100, 3110 --UNION ALL
            ) h(Parent, Tag)
LEFT JOIN   (
            SELECT      1                   AS FirstTag
                        , 3000              AS LastTag
                        , c.*
            FROM        #Customers c
            ) c
ON          h.Tag BETWEEN c.FirstTag AND c.LastTag
LEFT JOIN   (
            SELECT      3100                AS FirstTag
                        , 3110              AS LastTag
                        , c.Name            AS CustomerName
                        , p.Name
            FROM        #Customers c
            JOIN        #Projects p
            ON          c.ID = p.Customer_ID
            ) p
ON          h.Tag BETWEEN p.FirstTag AND p.LastTag
ORDER BY    COALESCE(p.CustomerName, c.Name), p.Name, h.Tag
FOR XML EXPLICIT

DROP TABLE #Customers, #Projects

Produces this

<Customer>
  <Name>john</Name>
  <City>Mumbai</City>
  <Projects>
    <Project>
      <Project_Name>pqr</Project_Name>
    </Project>
  </Projects>
</Customer>
<Customer>
  <Name>Rocky</Name>
  <City>Delhi</City>
  <Projects>
    <Project>
      <Project_Name>abc</Project_Name>
    </Project>
    <Project>
      <Project_Name>lmn</Project_Name>
    </Project>
  </Projects>
</Customer>
<Customer>
  <Name>Stan</Name>
  <City>New York</City>
  <Projects />
</Customer>

Notice this differs from your original request: under Projects there are separate Projects that wrap project name. This works on sql2000 too.

Upvotes: 0

dan radu
dan radu

Reputation: 2782

I have rebuilt the rowset based on your Xml, and using FOR XML PATH you can construct your Xml as you like (upvote for marc_s):

DECLARE @x XML = '
<Customer Name="john" City="Mumbai">
  <Project Project_Name="pqr" />
</Customer>
<Customer Name="Rocky" City="Delhi">
  <Project Project_Name="abc" />
  <Project Project_Name="lmn" />
</Customer>
'

SELECT 
    c.Name AS 'name'
    , c.City AS 'city'
    , (SELECT
        p.Project_Name AS 'Project_Name'
    FROM (
    SELECT c.value('../@Name', 'VARCHAR(50)') AS CustomerName
    , c.value('./@Project_Name', 'VARCHAR(50)') AS Project_Name
    FROM @x.nodes('//Project') AS t(c)
    ) p WHERE c.Name = p.CustomerName 
    FOR XML PATH('project'), TYPE) AS 'Projects'
FROM 
(
SELECT c.value('./@Name', 'VARCHAR(50)') AS Name
    , c.value('./@City', 'VARCHAR(50)') AS City
FROM @x.nodes('//Customer') AS t(c)
) c
FOR XML PATH('Customer')

Upvotes: 0

marc_s
marc_s

Reputation: 755381

You need to look at the FOR XML PATH option that SQL Server 2005 introduced - see the What's New in FOR XML in Microsoft SQL Server 2005 document for more information.

Basically, with FOR XML PATH, you can define the shape of your XML very easily. You can define certain structures, you can define certain columns to be output as attributes, and others as elements - totally under your control.

Not knowing your table structure, I can only guess what the tables and columns are called in your case - but you could probably write something like:

SELECT 
    c.ID AS '@ID',   -- define output as attribute on node
    c.Name,  -- if you don't specify anything -> output as element of the same name
    c.City,
    (SELECT
         p.Name as 'Project_Name',   -- define different XML element name for column
         p.DueDate
     FROM 
         dbo.Project p
     WHERE
         p.CustomerID = c.ID
     FOR XML PATH('Project'), TYPE
    ) AS 'Projects'
FROM
    dbo.Customer c
FOR XML PATH('Customer'), ROOT('AllCustomers')

Upvotes: 3

Related Questions