TechSapphire Wednesday, 2018-05-23, 4:02 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Main » 2014 » June » 10 » SQL XML Queries
    2:26 AM
    SQL XML Queries

    SQL XML:

    You can generate XML from SQL queries without even doing single line of code in your programming language. Below is the script used in above video:

    --Query 1

    select 1 as tag,null as parent,
    c.customerid as [Customers!1!Customerid],
    null as [order!2!orderid]
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 2 as tag,1 as parent,
    c.customerid,
    o.orderid
    from customers c inner join orders o
    on o.customerid=c.customerid
    order by [Customers!1!Customerid],[order!2!orderid]
    for xml auto

    --Query 2

    select 1 as tag,null as parent,
    c.customerid as [Customers!1!Customerid],
    null as [order!2!orderid]
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 2 as tag,1 as parent,
    c.customerid,
    o.orderid
    from customers c inner join orders o
    on o.customerid=c.customerid
    order by [Customers!1!Customerid],[order!2!orderid]
    for xml explicit

    --Query 3

    select 1 as tag,null as parent,
    c.customerid as [Customers!1!Customerid],
    null as [order!2!orderid!ELEMENT]
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 2 as tag,1 as parent,
    c.customerid,
    o.orderid
    from customers c inner join orders o
    on o.customerid=c.customerid
    order by [Customers!1!Customerid],[order!2!orderid!ELEMENT]
    for xml explicit

    --Query 4

    select 1 as tag,null as parent,
    c.customerid as [Customers!1!Customerid],
    null as [order!2!orderid],
    null as [order!2!Name!ELEMENT],
    null as [order!2!Frieght!ELEMENT]
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 2 as tag,1 as parent,
    c.customerid,
    o.orderid,
    o.shipname,
    o.freight
    from customers c inner join orders o
    on o.customerid=c.customerid
    order by [Customers!1!Customerid],[order!2!orderid],[order!2!Name!ELEMENT],
    [order!2!Frieght!ELEMENT]
    for xml explicit

    --Query 5


    select 1 as tag,null as parent,
    c.customerid as [Customers!1!Customerid],
    c.address as [Customers!1!Addresses],
    c.city as [Customers!1!City],
    null as [Orders!2],
    null as [order!3!orderid],
    null as [order!3!shipname!ELEMENT],
    null as [order!3!freight!ELEMENT]
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 2 as tag,1 as parent,
    c.customerid,
    null,
    null,
    null,
    null,
    null,
    null
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 3 as tag,2 as parent,
    c.customerid,
    null,
    null,
    null,
    o.orderid,
    o.shipname,
    o.freight
    from customers c inner join orders o
    on o.customerid=c.customerid
    order by [Customers!1!Customerid],[order!3!orderid],[order!3!shipname!ELEMENT],
    [order!3!freight!ELEMENT]
    for xml explicit

    --Query 6


    select 1 as tag, null as parent,
    null as [Custmers!1],
    null as [Customer!2!Cusotmerid],
    null as [Orders!3],
    null as [Order!4!Orderid],
    null as [Order!4!ShipName!ELEMENT],
    null as [Order!4!Freight!ELEMENT]
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 2 as tag, 1 as parent,
    null,
    c.customerid,
    null,
    null,
    null,
    null
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 3 as tag,2 as parent,
    '',
    c.customerid,
    '',
    null,
    null,
    null
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 4 as tag,3 as parent,
    '',
    c.customerid,
    '',
    o.orderid,
    o.shipname,
    o.freight
    from customers c inner join orders o
    on o.customerid=c.customerid
    order by  [Customer!2!Cusotmerid],[Order!4!Orderid],[Order!4!ShipName!ELEMENT], [Order!4!Freight!ELEMENT]
    for xml explicit

    --Query 7


    select 1 as tag, null as parent,
    null as [Custmers!1],
    null as [Customer!2!Cusotmerid],
    null as [Orders!3],
    null as [CustomerDetail!4],
    null as [CustomerDetail!4!Address!ELEMENT],
    null as [CustomerDetail!4!City!ELEMENT],
    null as [Order!5!Orderid],
    null as [Order!5!ShipName!ELEMENT],
    null as [Order!5!Freight!ELEMENT]
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 2 as tag, 1 as parent,
    null,
    c.customerid,
    null,
    null,
    null,
    null,
    null,
    null,
    null
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 3 as tag,2 as parent,
    '',
    c.customerid,
    '',
    null,
    null,
    null,
    null,
    null,
    null
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 4 as tag,2 as parent,
    '',
    c.customerid,
    '',
    '',
    c.address,
    c.city,
    null,
    null,
    null
    from customers c inner join orders o
    on o.customerid=c.customerid
    union
    select 5 as tag,3 as parent,
    '',
    c.customerid,
    '',
    null,
    null,
    null,
    o.orderid,
    o.shipname,
    o.freight
    from customers c inner join orders o
    on o.customerid=c.customerid
    order by  [Customer!2!Cusotmerid],[CustomerDetail!4!Address!ELEMENT],
    [CustomerDetail!4!City!ELEMENT],[Order!5!Orderid],[Order!5!ShipName!ELEMENT], [Order!5!Freight!ELEMENT]
    for xml explicit

     

     

    Category: Database | Views: 8616 | Added by: Admin | Tags: SQL XML, sql, queries, SQL/XML, xml | Rating: 0.0/0
    Total comments: 0
    Only registered users can add comments.
    [ Registration | Login ]
    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018