May 23, 2011 at 2:53 pm
I have the following xml document.
DECLARE @doc XML
SET @doc = N'<?xml version="1.0" encoding="UTF-16" ?>
<root name="TABLE_NAME_1">
<row action="U" timestamp="2011-05-18 17:14:21.000">
<column name="CRCUSN">86831</column>
<column name="CRSYER">111</column>
<column name="CRSFPD">1</column>
<column name="CRTSLD">3004.81</column>
<column name="CRTCRS">39.71</column>
<column name="CRFGAWD">148.21</column>
<column name="CRFGAVL">.01</column>
<column name="CRCRW#"></column>
<column name="CRFGVL">2001</column>
<column name="CRFGPC">5.01</column>
<column name="CREXPFL"></column>
</row>
<row>...</row>
.
.
</root>'
I have a sql table where I wish to insert the column values for each row. The sql table is defined like this:
CREATE TABLE [dbo].[TABLE_NAME_1](
[CRCUSN] [decimal](6, 0) NOT NULL,
[CRSYER] [decimal](3, 0) NOT NULL,
[CRSFPD] [decimal](2, 0) NOT NULL,
[CRTSLD] [decimal](9, 2) NOT NULL,
[CRTCRS] [decimal](9, 2) NOT NULL,
[CRFGAWD] [decimal](9, 2) NOT NULL,
[CRFGAVL] [decimal](9, 2) NOT NULL,
[CRCRW#] [varchar](15) NOT NULL,
[CRFGVL] [decimal](7, 0) NOT NULL,
[CRFGPC] [decimal](4, 2) NOT NULL,
[CREXPFL] [varchar](1) NOT NULL
)
When I issue the following query:
select row_number() over(order by t.c.value('../row[1]', 'nvarchar(10)')) as row_num,
t.c.value('../row[1]', 'nvarchar(20)') as row_id,
t.c.value('@name', 'nvarchar(100)') as column_name,
t.c.value('.', 'nvarchar(100)') as column_value
from @doc.nodes('/tpsfile/row') t(c)
The column_value = 8683111113004.8139.71148.21.0120015.01
Which is just a concatination of all the values for all the columns.
I am unable to figure out the proper XQuery to be able to insert the column values. The XML document contains multiple rows of data most of the time.
Maybe changing the xml document definition would help. Any suggestions greatly appreciated. Thank you.
May 23, 2011 at 3:27 pm
Check out this article: Loading XML Data into SQL Server (SQL Spackle)[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 23, 2011 at 3:37 pm
Thanks for the link. I did not come across this article, though I have been searching the Internet.
I'll read through this and get back on this topic.
May 23, 2011 at 4:09 pm
The link only gets you so far, as you have to pivot the results because your elements are key-value pairs instead of named elements.
I saw nothing in the sample xml that uniquely identified a row, so I used the timestamp of each row node to separate them. Not sure of that meets your requirements, but this will turn your xml into something that can be inserted into a table:
DECLARE @doc XML
SET @doc = N'<?xml version="1.0" encoding="UTF-16" ?>
<root name="TABLE_NAME_1">
<row action="U" timestamp="2011-05-18 17:14:21.000">
<column name="CRCUSN">86831</column>
<column name="CRSYER">111</column>
<column name="CRSFPD">1</column>
<column name="CRTSLD">3004.81</column>
<column name="CRTCRS">39.71</column>
<column name="CRFGAWD">148.21</column>
<column name="CRFGAVL">.01</column>
<column name="CRCRW#"></column>
<column name="CRFGVL">2001</column>
<column name="CRFGPC">5.01</column>
<column name="CREXPFL"></column>
</row>
<row action="U" timestamp="2011-05-18 17:15:21.000">
<column name="CRCUSN">10000</column>
<column name="CRSYER">555</column>
<column name="CRSFPD">1</column>
<column name="CRTSLD">100.0</column>
<column name="CRTCRS">55.55</column>
<column name="CRFGAWD">222.22</column>
<column name="CRFGAVL">.05</column>
<column name="CRCRW#">foo</column>
<column name="CRFGVL">999</column>
<column name="CRFGPC">12.22</column>
<column name="CREXPFL">x</column>
</row>
</root>';
WITH ColVals AS(
SELECT t.c.value('../@timestamp', 'char(23)') AS row_id,
t.c.value('../@action', 'char(1)') AS RowAction,
t.c.value('@name', 'nvarchar(100)') AS column_name,
t.c.value('(./text())[1]', 'nvarchar(100)') AS column_value
FROM @doc.nodes('/root/row/column') t(c)
)
SELECT row_num, RowAction, CRCUSN, CRSYER, CRSFPD, CRTSLD, CRTCRS, CRFGAWD, CRFGAVL, [CRCRW#], CRFGVL, CRFGPC, CREXPFL
FROM (SELECT DENSE_RANK() OVER (ORDER BY row_id) AS row_num, row_id, RowAction, column_name, column_value
FROM ColVals
) a
PIVOT(max(column_value) FOR column_name IN (CRCUSN, CRSYER, CRSFPD, CRTSLD, CRTCRS, CRFGAWD, CRFGAVL, [CRCRW#], CRFGVL, CRFGPC, CREXPFL)
) pvt
-Eddie
Eddie Wuerch
MCM: SQL
May 23, 2011 at 4:28 pm
Borrowing Eddie's excellent sample data...here is an alternative method without the pivot
DECLARE @doc XML
SET @doc = N'<?xml version="1.0" encoding="UTF-16" ?>
<root name="TABLE_NAME_1">
<row action="U" timestamp="2011-05-18 17:14:21.000">
<column name="CRCUSN">86831</column>
<column name="CRSYER">111</column>
<column name="CRSFPD">1</column>
<column name="CRTSLD">3004.81</column>
<column name="CRTCRS">39.71</column>
<column name="CRFGAWD">148.21</column>
<column name="CRFGAVL">.01</column>
<column name="CRCRW#"></column>
<column name="CRFGVL">2001</column>
<column name="CRFGPC">5.01</column>
<column name="CREXPFL"></column>
</row>
<row action="U" timestamp="2011-05-18 17:15:21.000">
<column name="CRCUSN">10000</column>
<column name="CRSYER">555</column>
<column name="CRSFPD">1</column>
<column name="CRTSLD">100.0</column>
<column name="CRTCRS">55.55</column>
<column name="CRFGAWD">222.22</column>
<column name="CRFGAVL">.05</column>
<column name="CRCRW#">foo</column>
<column name="CRFGVL">999</column>
<column name="CRFGPC">12.22</column>
<column name="CREXPFL">x</column>
</row>
</root>';
SELECT
nd.value('@action','char(1)') as [action]
, nd.value('@timestamp','datetime') as [timestamp]
, nd.value('(column[@name="CRCUSN"]/text())[1]','int') as [CRCUSN]
, nd.value('(column[@name="CRSYER"]/text())[1]','int') as [CRSYER]
, nd.value('(column[@name="CRSFPD"]/text())[1]','int') as [CRSFPD]
, nd.value('(column[@name="CRTSLD"]/text())[1]','decimal(12,2)') as [CRTSLD]
, nd.value('(column[@name="CRTCRS"]/text())[1]','decimal(12,2)') as [CRTCRS]
, nd.value('(column[@name="CRFGAWD"]/text())[1]','decimal(12,2)') as [CRFGAWD]
, nd.value('(column[@name="CRFGAVL"]/text())[1]','decimal(12,2)') as [CRFGAVL]
, nd.value('(column[@name="CRCRW#"]/text())[1]','nvarchar(10)') as [CRCRW#]
, nd.value('(column[@name="CRFGVL"]/text())[1]','int') as [CRFGVL]
, nd.value('(column[@name="CRFGPC"]/text())[1]','decimal(12,2)') as [CRFGPC]
, nd.value('(column[@name="CREXPFL"]/text())[1]','varchar(10)') as [CREXPFL]
FROM @doc.nodes('//row') x(nd)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
May 24, 2011 at 4:29 pm
Thank you! Both, the pivot and the XQuery solutions work.
After some deliberation I've ended up using the XQuery solution.
Main reason being that I am not a fan of the SQL pivot syntax. It always takes me a long time to get the pivots working. I was looking at this from a maintenance point of view, and I will have an easier time maintaining the XQuery.
I did not test if one performs better than the other.
May 24, 2011 at 5:12 pm
sandor.pakh (5/24/2011)
Thank you! Both, the pivot and the XQuery solutions work.After some deliberation I've ended up using the XQuery solution.
Main reason being that I am not a fan of the SQL pivot syntax. It always takes me a long time to get the pivots working. I was looking at this from a maintenance point of view, and I will have an easier time maintaining the XQuery.
I did not test if one performs better than the other.
You're welcome - hopefully others will find uses for both methods and choose whichever suits them best as well.
I like your point about the PIVOT syntax being harder to maintain - I don't use it often and always have to look it up before I do, it's just not intuitive for me :w00t:.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply