Solved

Loading generated XML to a table

Posted on 2014-02-27
11
264 Views
Last Modified: 2014-03-03
I have the query below which produces the XML I need.  The problem is I want to insert it into a table or a variable.  How is this best done from SQL server?

SELECT
            1 AS 'Tag'
            ,NULL As 'Parent'
            ,0 AS 'Branches!1!Sort!hide'
            ,NULL AS 'Branches!1!'
            ,NULL AS 'Branch!2!BranchNumber!ID'
            ,NULL AS 'Branch!2!BranchName'
            ,NULL AS 'Branch!2!BranchDistrict'
            ,NULL AS 'Branch!2!BranchRegion'
            ,NULL AS 'Branch!2!BranchZone'
            ,NULL AS 'Branch!2!BranchCountry'
            ,NULL AS 'Branch!2!BranchCountryNo'
            ,NULL AS 'Despatchs!3!ELEMENT'
            ,NULL AS 'Stock!5!ELEMENT'
            ,NULL AS 'Despatch!4!LongCode!ID'
            ,NULL AS 'Despatch!4!Units'
            ,NULL AS 'Despatch!4!Memo'
            ,NULL AS 'Despatch!4!TransactionType'
            ,NULL AS 'Despatch!4!OtherBranchNumber'
            ,NULL AS 'Item!6!LongCode!ID'
            ,NULL AS 'Item!6!ShortCode'
            ,NULL AS 'Item!6!ProductDescription'
            ,NULL AS 'Item!6!BranchSellingPrice'
            ,NULL AS 'Item!6!CurrentStockUnits'
            ,NULL AS 'Item!6!ToPickUnits'
UNION ALL
      SELECT DISTINCT
            2 AS Tag
            ,1 AS Parent
            ,br.BranchNumber * 100
            ,NULL
            ,br.BranchNumber
            ,br.BranchName
            ,br.BranchDistrict
            ,br.BranchRegion
            ,br.BranchZone
            ,br.BranchCountry
            ,br.BranchCountryNo
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
      WHERE di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
    3 AS Tag
    ,2 AS Parent
    ,BranchNumber * 100 + 1
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
SELECT DISTINCT
    4 AS Tag
    ,3 AS Parent
    ,BranchNumber * 100 + 2
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,LongCode
    ,Units
    ,TransactionType
    ,Memo
    ,OtherBranchNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
      SELECT DISTINCT
            5 AS Tag
            ,2 AS Parent
            ,BranchNumber * 100
            ,NULL
            ,BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
      UNION ALL
      SELECT DISTINCT
            6 AS Tag
            ,5 AS Parent
            ,di.BranchNumber * 100
            ,NULL
            ,di.BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,br.LongCode
            ,br.ShortCode
            ,br.ProductDescription
            ,br.BranchSellingPrice
            ,br.CurrentStockUnits
            ,br.ToPickUnits
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
      WHERE di.BranchNumber = 15
ORDER BY [Branches!1!Sort!hide]
FOR XML EXPLICIT)
0
Comment
Question by:Alyanto
  • 5
  • 4
  • 2
11 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39892224
declare
	@xml_string varchar(max)
SET
	@xml_string=(
SELECT
            1 AS 'Tag'
            ,NULL As 'Parent'
            ,0 AS 'Branches!1!Sort!hide'
            ,NULL AS 'Branches!1!'
            ,NULL AS 'Branch!2!BranchNumber!ID'
            ,NULL AS 'Branch!2!BranchName'
            ,NULL AS 'Branch!2!BranchDistrict'
            ,NULL AS 'Branch!2!BranchRegion'
            ,NULL AS 'Branch!2!BranchZone'
            ,NULL AS 'Branch!2!BranchCountry'
            ,NULL AS 'Branch!2!BranchCountryNo'
            ,NULL AS 'Despatchs!3!ELEMENT'
            ,NULL AS 'Stock!5!ELEMENT'
            ,NULL AS 'Despatch!4!LongCode!ID'
            ,NULL AS 'Despatch!4!Units'
            ,NULL AS 'Despatch!4!Memo'
            ,NULL AS 'Despatch!4!TransactionType'
            ,NULL AS 'Despatch!4!OtherBranchNumber'
            ,NULL AS 'Item!6!LongCode!ID'
            ,NULL AS 'Item!6!ShortCode'
            ,NULL AS 'Item!6!ProductDescription'
            ,NULL AS 'Item!6!BranchSellingPrice'
            ,NULL AS 'Item!6!CurrentStockUnits'
            ,NULL AS 'Item!6!ToPickUnits'
UNION ALL
      SELECT DISTINCT
            2 AS Tag
            ,1 AS Parent
            ,br.BranchNumber * 100
            ,NULL 
            ,br.BranchNumber
            ,br.BranchName 
            ,br.BranchDistrict
            ,br.BranchRegion
            ,br.BranchZone 
            ,br.BranchCountry 
            ,br.BranchCountryNo 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
    3 AS Tag
    ,2 AS Parent
    ,BranchNumber * 100 + 1
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
SELECT DISTINCT
    4 AS Tag
    ,3 AS Parent
    ,BranchNumber * 100 + 2
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,LongCode
    ,Units
    ,TransactionType
    ,Memo
    ,OtherBranchNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
      SELECT DISTINCT
            5 AS Tag
            ,2 AS Parent
            ,BranchNumber * 100
            ,NULL
            ,BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
      UNION ALL
      SELECT DISTINCT
            6 AS Tag
            ,5 AS Parent
            ,di.BranchNumber * 100
            ,NULL
            ,di.BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,br.LongCode
            ,br.ShortCode
            ,br.ProductDescription 
            ,br.BranchSellingPrice 
            ,br.CurrentStockUnits
            ,br.ToPickUnits 
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
ORDER BY [Branches!1!Sort!hide]
FOR XML EXPLICIT)

SELECT @xml_string

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39892236
To insert into an XML variable:
declare
	@xml xml
SET
	@xml=CONVERT(xml, (
SELECT
            1 AS 'Tag'
            ,NULL As 'Parent'
            ,0 AS 'Branches!1!Sort!hide'
            ,NULL AS 'Branches!1!'
            ,NULL AS 'Branch!2!BranchNumber!ID'
            ,NULL AS 'Branch!2!BranchName'
            ,NULL AS 'Branch!2!BranchDistrict'
            ,NULL AS 'Branch!2!BranchRegion'
            ,NULL AS 'Branch!2!BranchZone'
            ,NULL AS 'Branch!2!BranchCountry'
            ,NULL AS 'Branch!2!BranchCountryNo'
            ,NULL AS 'Despatchs!3!ELEMENT'
            ,NULL AS 'Stock!5!ELEMENT'
            ,NULL AS 'Despatch!4!LongCode!ID'
            ,NULL AS 'Despatch!4!Units'
            ,NULL AS 'Despatch!4!Memo'
            ,NULL AS 'Despatch!4!TransactionType'
            ,NULL AS 'Despatch!4!OtherBranchNumber'
            ,NULL AS 'Item!6!LongCode!ID'
            ,NULL AS 'Item!6!ShortCode'
            ,NULL AS 'Item!6!ProductDescription'
            ,NULL AS 'Item!6!BranchSellingPrice'
            ,NULL AS 'Item!6!CurrentStockUnits'
            ,NULL AS 'Item!6!ToPickUnits'
UNION ALL
      SELECT DISTINCT
            2 AS Tag
            ,1 AS Parent
            ,br.BranchNumber * 100
            ,NULL 
            ,br.BranchNumber
            ,br.BranchName 
            ,br.BranchDistrict
            ,br.BranchRegion
            ,br.BranchZone 
            ,br.BranchCountry 
            ,br.BranchCountryNo 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
    3 AS Tag
    ,2 AS Parent
    ,BranchNumber * 100 + 1
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
SELECT DISTINCT
    4 AS Tag
    ,3 AS Parent
    ,BranchNumber * 100 + 2
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,LongCode
    ,Units
    ,TransactionType
    ,Memo
    ,OtherBranchNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
      SELECT DISTINCT
            5 AS Tag
            ,2 AS Parent
            ,BranchNumber * 100
            ,NULL
            ,BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
      UNION ALL
      SELECT DISTINCT
            6 AS Tag
            ,5 AS Parent
            ,di.BranchNumber * 100
            ,NULL
            ,di.BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,br.LongCode
            ,br.ShortCode
            ,br.ProductDescription 
            ,br.BranchSellingPrice 
            ,br.CurrentStockUnits
            ,br.ToPickUnits 
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
ORDER BY [Branches!1!Sort!hide]
FOR XML EXPLICIT))

SELECT @xml

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39892238
From variable you can insert into a table easily. You cant insert directly from query if you use FOR XML.
0
 

Author Comment

by:Alyanto
ID: 39892286
Option one resulted in this message

Msg 1086, Level 15, State 1, Line 166
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.


Option Two Resulted in this message
Msg 1086, Level 15, State 1, Line 166
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.


A little cryptic but I think it means

SELECT * FROM ( SELECT ... FROM ADateSource) AS DAT or similar for each sub query, or am I barking up the wrong tree?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39892310
Ok, instead of set use select:

declare
	@xml_string varchar(max)
SELECT
	@xml_string=
...(

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Alyanto
ID: 39892333
My choices seem to be put the data in a table and deal with it from there, export it to a file and deal with it from there or send it to a variable.  I would be able to work this through with most of those options.

I tried the encasing the sub queries this did not work!
0
 

Author Comment

by:Alyanto
ID: 39892344
I tried the third option proposed and this was the result:

Msg 1086, Level 15, State 1, Line 167
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39892379
Can you post the query you tried here?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39893623
The part that you are missing is:
1. FOR XML EXPLICIT, TYPE
2. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39893627
In other words, try it this way:
DECLARE @X xml 

SELECT @X = 
(SELECT *
FROM (
SELECT  1 AS 'Tag',
        NULL AS 'Parent',
        0 AS 'Branches!1!Sort!hide',
        NULL AS 'Branches!1!',
        NULL AS 'Branch!2!BranchNumber!ID',
        NULL AS 'Branch!2!BranchName',
        NULL AS 'Branch!2!BranchDistrict',
        NULL AS 'Branch!2!BranchRegion',
        NULL AS 'Branch!2!BranchZone',
        NULL AS 'Branch!2!BranchCountry',
        NULL AS 'Branch!2!BranchCountryNo',
        NULL AS 'Despatchs!3!ELEMENT',
        NULL AS 'Stock!5!ELEMENT',
        NULL AS 'Despatch!4!LongCode!ID',
        NULL AS 'Despatch!4!Units',
        NULL AS 'Despatch!4!Memo',
        NULL AS 'Despatch!4!TransactionType',
        NULL AS 'Despatch!4!OtherBranchNumber',
        NULL AS 'Item!6!LongCode!ID',
        NULL AS 'Item!6!ShortCode',
        NULL AS 'Item!6!ProductDescription',
        NULL AS 'Item!6!BranchSellingPrice',
        NULL AS 'Item!6!CurrentStockUnits',
        NULL AS 'Item!6!ToPickUnits'
UNION ALL
SELECT DISTINCT
        2 AS Tag,
        1 AS Parent,
        br.BranchNumber * 100,
        NULL,
        br.BranchNumber,
        br.BranchName,
        br.BranchDistrict,
        br.BranchRegion,
        br.BranchZone,
        br.BranchCountry,
        br.BranchCountryNo,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
FROM    dbo.PI_Dispatch di
        INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
WHERE   di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
        3 AS Tag,
        2 AS Parent,
        BranchNumber * 100 + 1,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
FROM    dbo.PI_Dispatch
WHERE   BranchNumber = 15
UNION ALL
SELECT DISTINCT
        4 AS Tag,
        3 AS Parent,
        BranchNumber * 100 + 2,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        LongCode,
        Units,
        TransactionType,
        Memo,
        OtherBranchNumber,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
FROM    dbo.PI_Dispatch
WHERE   BranchNumber = 15
UNION ALL
SELECT DISTINCT
        5 AS Tag,
        2 AS Parent,
        BranchNumber * 100,
        NULL,
        BranchNumber AS Trial,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
FROM    dbo.PI_Dispatch
WHERE   BranchNumber = 15
UNION ALL
SELECT DISTINCT
        6 AS Tag,
        5 AS Parent,
        di.BranchNumber * 100,
        NULL,
        di.BranchNumber AS Trial,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        br.LongCode,
        br.ShortCode,
        br.ProductDescription,
        br.BranchSellingPrice,
        br.CurrentStockUnits,
        br.ToPickUnits
FROM    dbo.PI_Dispatch di
        INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
WHERE   di.BranchNumber = 15
) a ORDER BY [Branches!1!Sort!hide] FOR XML EXPLICIT)

Open in new window

0
 

Author Closing Comment

by:Alyanto
ID: 39899840
Thank you Anthony for your answer this has salvaged a considerable amount of work to date.  I would also like to acknowledge the contribution of the Zberteoc who was clearly going in the right direction.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now