XML data sandwich - 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 2008 Forums

Transact-SQL (2008)

XML data sandwich

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2012-04-04 : 17:16:39

Hi,

I have a table with only XML data in, the headers are not always the same in each XML. I'd like to pivot all possible headers into columns, like opening an XML file in Excel. Is this possible?

Alternatively if not possible, I was thinking of joining the rows into one large XML (by stripping the opening and close headers except 1st and last row), then getting the Excel tool to make the table. Not sure if these ideas are valid? Example below with different headers, but the full XML has hundreds of headers/columns.


CREATE TABLE #XMLFix(
	intPK INT IDENTITY(1,1),
	[XMLfix] [xml] NULL
) ON [PRIMARY]

INSERT INTO #XMLFix
        (XMLfix )
SELECT  ( '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
       <Output xmlns="http://testxml.com">
            <ID>12345678</ID>
    </Output>
  </soap:Body>
</soap:Envelope>') 
UNION ALL
SELECT(
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
       <Output xmlns="http://testxml.com">
            <ID>12345679</ID>
            <Name>Bob</Name>
    </Output>
  </soap:Body>
</soap:Envelope>')UNION ALL
SELECT(
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
       <Output xmlns="http://testxml.com">
            <ID>12345680</ID>
            <Location>Vienna</Location>
    </Output>
  </soap:Body>
</soap:Envelope>')

SELECT * FROM #XMLFix

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-04 : 17:26:35

what is your ultimate aim before we talk about possible methods of doing this.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Humate
Posting Yak Master

101 Posts

Posted - 2012-04-04 : 17:34:03

I'm aiming to pivot all possible data headers to columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-04 : 18:38:18

can you post your expected output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-04 : 18:45:56

pivot all possible data headers to columns from #XMLFix or from the xml?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Humate
Posting Yak Master

101 Posts

Posted - 2012-04-05 : 11:11:18

Sorry, I mean pivot all the headers in the xml file. i.e.


ID              Name         Location
12345678        
12345679        Bob
12345680                      Vienna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 11:55:13

[code]
SELECT p.q.value('(./ID)[1]','integer') AS ID,
p.q.value('(./Name)[1]','varchar(100)') AS Name,
p.q.value('(./Location)[1]','varchar(100)') AS Location
FROM #XMLFix x
CROSS APPLY [XMLfix].nodes('declare xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/";/soap:Envelope/soap:Body/Output')p(q)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Humate
Posting Yak Master

101 Posts

Posted - 2012-04-05 : 12:08:25

Sorry perhaps I didn't explain well enough. In my XML there are hundreds of headers/columns, but the same ones don't appear in each XML.

I want to find all possible headers first, like a dynamic pivot, rather than delaring each one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources