Solved

Loading generated XML to a table

Posted on 2014-02-27
11
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 27

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 27

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 27

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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 1

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 27

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
 
LVL 1

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
 
LVL 1

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 27

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
 
LVL 1

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Server 2012 r2 and SQL 2014 6 34
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 64
when sa SQL Login becomes locked 6 34
Query Task 8 23
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

752 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