Reading XML file structure with column id with spaces

DECLARE  @XML xml
set @XML = '<root>
  <Units>
    <Unit id="un1">
      <UnitName>Dollars</UnitName>
      <Scale>0</Scale>
      <Symbol>$</Symbol>
      <Prefix>T</Prefix>
      <Pad>F</Pad>
      <UnitSeq>1</UnitSeq>
      <RptSeq>1</RptSeq>
    </Unit>
	</Units>
	</root>'

CREATE TABLE #tmp_user
(UserID INT,SecurityXML XML)

INSERT INTO #tmp_user
        ( UserID, SecurityXML )
VALUES  ( 1, 
          @XML
          )


SELECT * FROM #tmp_user

What I like to try and accomplish is read the data but I am getting stuck due to having a space in the Name i.e Unit Id

I tried many examples and any help is appreciated.

If I could get it into a table that would great.

SELECT
	SecurityXML.value('(//Unit/@id)[1]','VARCHAR(32)')
FROM 
	#tmp_user

The "Unit id" indicates an element with the name Unit which has an attribute named id.

2 Likes

Thank you so much

if there is multiple units i.e 2
<Units>
    <Unit id="un1">
      <UnitName>Dollars</UnitName>
      <Scale>0</Scale>
      <Symbol>$</Symbol>
      <Prefix>T</Prefix>
      <Pad>F</Pad>
      <UnitSeq>1</UnitSeq>
      <RptSeq>1</RptSeq>
    </Unit>
	<Unit id="un2">
			<UnitName>Hours</UnitName>
			<Scale>0</Scale>
			<Symbol>H</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>2</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
	</Units>
	</root>'

SELECT
SecurityXML.value('(//Unit/@id)[1]','VARCHAR(32)') as 'Unit',
SecurityXML.value('(//UnitName)[1]','VARCHAR(32)') as 'UnitName',
SecurityXML.value('(//Scale)[1]','VARCHAR(32)') as 'Scale',
SecurityXML.value('(//Symbol)[1]','VARCHAR(32)') as 'Symbol',
SecurityXML.value('(//Prefix)[1]','VARCHAR(32)') as 'Prefix',
SecurityXML.value('(//Pad)[1]','VARCHAR(32)') as 'Pad',
SecurityXML.value('(//UnitSeq)[1]','VARCHAR(32)') as 'UnitSeq',
SecurityXML.value('(//RptSeq)[1]','VARCHAR(32)') as 'RptSeq'

FROM
#tmp_user

I got one row out, and there may be many units, i.e un1 and un2 and up to x
Without coding all of them is there a way to dynamic obtain the number and loop through the levels.

I got this far

DECLARE @x XML

SET @x = 
'<Units>
    <Unit id="un1">
      <UnitName>Dollars</UnitName>
      <Scale>0</Scale>
      <Symbol>$</Symbol>
      <Prefix>T</Prefix>
      <Pad>F</Pad>
      <UnitSeq>1</UnitSeq>
      <RptSeq>1</RptSeq>
    </Unit>
	<Unit id="un2">
			<UnitName>Hours</UnitName>
			<Scale>0</Scale>
			<Symbol>H</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>2</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
	</Units>'
 

-- Total count of <Employee> Nodes

DECLARE @max INT, @i INT

SELECT 

    @max = @x.query('<e>
                           { count(/Units/Unit) }

                    </e>').value('e[1]','int') 

-- Set counter variable to 1

SET @i = 1 -- variable to store employee name

DECLARE @UnitName VARCHAR(10)
 
-- loop starts

WHILE @i <= @max BEGIN

    -- select "Name" to the variable

    SELECT
  	 @UnitName=	x.value('(UnitName)[1]','VARCHAR(32)') 
	FROM 

    @x.nodes('/Units/Unit[position()=sql:variable("@i")]') 

    e(x)

     -- print the name

    PRINT @UnitName

 

    -- increment counter

    SET @i = @i + 1        

END

---Two rows of data came out
Dollars
Hours

How to add another part scale, and symbol without multiple while loops

Quick Update

DECLARE @x XML

SET @x = 
'<Units>
    <Unit id="un1">
      <UnitName>Dollars</UnitName>
      <Scale>0</Scale>
      <Symbol>$</Symbol>
      <Prefix>T</Prefix>
      <Pad>F</Pad>
      <UnitSeq>1</UnitSeq>
      <RptSeq>1</RptSeq>
    </Unit>
	<Unit id="un2">
			<UnitName>Hours</UnitName>
			<Scale>2</Scale>
			<Symbol>H</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>2</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
	</Units>'
 

-- Total count of <Employee> Nodes

DECLARE @max INT, @i INT

SELECT 

    @max = @x.query('<e>
                           { count(/Units/Unit) }

                    </e>').value('e[1]','int') 

-- Set counter variable to 1

SET @i = 1 -- variable to store employee name

DECLARE @UnitName VARCHAR(10),	@Scale VARCHAR(10), @Symbol VARCHAR(10), @Prefix VARCHAR(10), @Pad VARCHAR(10), @UnitSeq VARCHAR(10), @RptSeq VARCHAR(10)
 
-- loop starts

WHILE @i <= @max BEGIN

    -- select "Name" to the variable

    SELECT
  	 @UnitName	=	x.value('(UnitName)[1]','VARCHAR(32)') ,
	 @Scale		=	x.value('(Scale)[1]','VARCHAR(32)') ,
	 @Symbol	=	x.value('(Symbol)[1]','VARCHAR(32)') ,
	 @Prefix	=	x.value('(Prefix)[1]','VARCHAR(32)') ,
	 @Pad		=	x.value('(Pad)[1]','VARCHAR(32)') ,
	 @UnitSeq	=	x.value('(UnitSeq)[1]','VARCHAR(32)') ,
	 @RptSeq	=	x.value('(RptSeq)[1]','VARCHAR(32)') 

	FROM 

    @x.nodes('/Units/Unit[position()=sql:variable("@i")]') 

    e(x)

     -- print the name

    SELECT  @UnitName as 'UnitName',  @Scale as 'Scale', @Symbol as 'Symbol', @Prefix as 'Prefix', @Pad as 'Pad', @UnitSeq as 'UnitSeq', @RptSeq as 'RptSeq'

 

    -- increment counter

    SET @i = @i + 1        

END

I got two rows out
UnitName Scale Symbol Prefix Pad UnitSeq RptSeq
Dollars 0 $ T F 1 1
UnitName Scale Symbol Prefix Pad UnitSeq RptSeq
Hours 2 H F T 2 0

How to get the Umit and Unit Id un1/un2 to come out in the select aswell.

You probably already know this best practice: try to avoid using loops if at all possible in SQL Server. For your example, I would do the following:

select 
	id = c1.value('@id','varchar(32)') ,
	UnitName = c1.value('UnitName[1]','varchar(32)') 
from
	@x.nodes('//Unit') T1(c1)

I am showing two values, you can add the others. All the elements do not have to be VARCHAR(32). You may need longer varchars, or you could use other data types if you know what they are/should be.

The way to think about it is that you are extracting a record set that consists of all the Unit elements in your XML and then applying the value clause to its elements. You can see this if you run this query

select 
	UnitElement = c1.query('.')
from
	@x.nodes('//Unit') T1(c1)
[quote="JamesK, post:7, topic:12987"]
id = c1.value('@id','varchar(32)') ,
[/quote]

How do I add your pieces to my code example.

DECLARE @x XML

SET @x = 
	'<Units>
		<Unit id="un1">
			<UnitName>Dollars</UnitName>
			<Scale>0</Scale>
			<Symbol>$</Symbol>
			<Prefix>T</Prefix>
			<Pad>F</Pad>
			<UnitSeq>1</UnitSeq>
			<RptSeq>1</RptSeq>
		</Unit>
		<Unit id="un2">
			<UnitName>Hours</UnitName>
			<Scale>0</Scale>
			<Symbol>H</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>2</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
		<Unit id="un3">
			<UnitName>EQP</UnitName>
			<Scale>0</Scale>
			<Symbol>ep</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>3</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
		</Units>'

DECLARE @max INT, @i INT, 
	    @maxStructures INT, @iStructure INT, 
		@maxColumns INT, @iColumn INT


SELECT     @max				= @x.query('<e> { count(/Units/Unit) }   </e>').value('e[1]','int') 
SELECT     @maxStructures	= @x.query('<e> { count(/Structures/Structure) } </e>').value('e[1]','int') 
SELECT     @maxColumns		= @x.query('<e> { count(/Columns/Column) } </e>').value('e[1]','int') 

SET @i = 1
SET @iStructure = 1 
SET @iColumn = 1




DECLARE @UnitID VARCHAR(10),@UnitName VARCHAR(10),	@Scale VARCHAR(10), @Symbol VARCHAR(10), @Prefix VARCHAR(10), @Pad VARCHAR(10), @UnitSeq VARCHAR(10), @RptSeq VARCHAR(10)



-- loop starts

WHILE @i <= @max BEGIN

    -- select "Name" to the variable

    SELECT
	
  	 @UnitName	=	x.value('(UnitName)[1]','VARCHAR(32)') ,
	 @Scale		=	x.value('(Scale)[1]','VARCHAR(32)') ,
	 @Symbol	=	x.value('(Symbol)[1]','VARCHAR(32)') ,
	 @Prefix	=	x.value('(Prefix)[1]','VARCHAR(32)') ,
	 @Pad		=	x.value('(Pad)[1]','VARCHAR(32)') ,
	 @UnitSeq	=	x.value('(UnitSeq)[1]','VARCHAR(32)') ,
	 @RptSeq	=	x.value('(RptSeq)[1]','VARCHAR(32)') 

	FROM 

    @x.nodes('/Units/Unit[position()=sql:variable("@i")]') 

    e(x)

    SELECT  @UnitID as 'Unit id', @UnitName as 'UnitName',  @Scale as 'Scale', @Symbol as 'Symbol', @Prefix as 'Prefix', @Pad as 'Pad', @UnitSeq as 'UnitSeq', @RptSeq as 'RptSeq'

    SET @i = @i + 1        

END

It gives me the entire string of XML I am trying to get just the un1 value or un2 value so it becomes
Unit id UnitName Scale Symbol Prefix Pad UnitSeq RptSeq
un1 Dollars 0 $ T F 1 1
un1

DECLARE @x XML

SET @x =
'

Dollars
0
$
T
F
1
1


Hours
0
H
F
T
2
0


EQP
0
ep
F
T
3
0

'

DECLARE @max INT, @i INT,
@maxStructures INT, @iStructure INT,
@maxColumns INT, @iColumn INT

SELECT @max = @x.query(' { count(/Units/Unit) } ').value('e[1]','int')
SELECT @maxStructures = @x.query(' { count(/Structures/Structure) } ').value('e[1]','int')
SELECT @maxColumns = @x.query(' { count(/Columns/Column) } ').value('e[1]','int')

SET @i = 1
SET @iStructure = 1
SET @iColumn = 1

DECLARE @UnitID VARCHAR(10),@UnitName VARCHAR(10), @Scale VARCHAR(10), @Symbol VARCHAR(10), @Prefix VARCHAR(10), @Pad VARCHAR(10), @UnitSeq VARCHAR(10), @RptSeq VARCHAR(10)

-- loop starts

WHILE @i <= @max BEGIN

-- select "Name" to the variable

SELECT
 @UnitId	=	x.value('(//Unit/@id)[1]','VARCHAR(32)'),
 @UnitName	=	x.value('(UnitName)[1]','VARCHAR(32)') ,
 @Scale		=	x.value('(Scale)[1]','VARCHAR(32)') ,
 @Symbol	=	x.value('(Symbol)[1]','VARCHAR(32)') ,
 @Prefix	=	x.value('(Prefix)[1]','VARCHAR(32)') ,
 @Pad		=	x.value('(Pad)[1]','VARCHAR(32)') ,
 @UnitSeq	=	x.value('(UnitSeq)[1]','VARCHAR(32)') ,
 @RptSeq	=	x.value('(RptSeq)[1]','VARCHAR(32)') 

FROM 

@x.nodes('/Units/Unit[position()=sql:variable("@i")]') 

e(x)

SELECT  @UnitID as 'Unit id', @UnitName as 'UnitName',  @Scale as 'Scale', @Symbol as 'Symbol', @Prefix as 'Prefix', @Pad as 'Pad', @UnitSeq as 'UnitSeq', @RptSeq as 'RptSeq'

SET @i = @i + 1        

END
indent preformatted text by 4 spaces

When this runs I get
un1 Dollars -----
un1 Hours etc
it should be un2 Hours

DECLARE @x XML

SET @x = 
	'<Units>
		<Unit id="un1">
			<UnitName>Dollars</UnitName>
			<Scale>0</Scale>
			<Symbol>$</Symbol>
			<Prefix>T</Prefix>
			<Pad>F</Pad>
			<UnitSeq>1</UnitSeq>
			<RptSeq>1</RptSeq>
		</Unit>
		<Unit id="un2">
			<UnitName>Hours</UnitName>
			<Scale>0</Scale>
			<Symbol>H</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>2</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
		<Unit id="un3">
			<UnitName>EQP</UnitName>
			<Scale>0</Scale>
			<Symbol>ep</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>3</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
		</Units>'

DECLARE @max INT, @i INT, 
	    @maxStructures INT, @iStructure INT, 
		@maxColumns INT, @iColumn INT


SELECT     @max				= @x.query('<e> { count(/Units/Unit) }   </e>').value('e[1]','int') 
SELECT     @maxStructures	= @x.query('<e> { count(/Structures/Structure) } </e>').value('e[1]','int') 
SELECT     @maxColumns		= @x.query('<e> { count(/Columns/Column) } </e>').value('e[1]','int') 

SET @i = 1
SET @iStructure = 1 
SET @iColumn = 1



DECLARE @UnitID VARCHAR(10),@UnitName VARCHAR(10),	@Scale VARCHAR(10), @Symbol VARCHAR(10), @Prefix VARCHAR(10), @Pad VARCHAR(10), @UnitSeq VARCHAR(10), @RptSeq VARCHAR(10)



-- loop starts

WHILE @i <= @max BEGIN

    -- select "Name" to the variable

    SELECT
	 @UnitId	=	x.value('(//Unit/@id)[1]','VARCHAR(32)'),
  	 @UnitName	=	x.value('(UnitName)[1]','VARCHAR(32)') ,
	 @Scale		=	x.value('(Scale)[1]','VARCHAR(32)') ,
	 @Symbol	=	x.value('(Symbol)[1]','VARCHAR(32)') ,
	 @Prefix	=	x.value('(Prefix)[1]','VARCHAR(32)') ,
	 @Pad		=	x.value('(Pad)[1]','VARCHAR(32)') ,
	 @UnitSeq	=	x.value('(UnitSeq)[1]','VARCHAR(32)') ,
	 @RptSeq	=	x.value('(RptSeq)[1]','VARCHAR(32)') 

	FROM 

    @x.nodes('/Units/Unit[position()=sql:variable("@i")]') 

    e(x)

    SELECT  @UnitID as 'Unit id', @UnitName as 'UnitName',  @Scale as 'Scale', @Symbol as 'Symbol', @Prefix as 'Prefix', @Pad as 'Pad', @UnitSeq as 'UnitSeq', @RptSeq as 'RptSeq'

    SET @i = @i + 1        

END

When it runs I get
un1 Dollars
un1 Hours

It should be un1 Dollars and
un2 Hours

It is not putting in the un2 from Unit ID

I tried 

 @UnitId	=	x.value('(Unit/@id)[1]','VARCHAR(32)'),

no loop approach.

DECLARE @x XML
declare @i int = 1

SET @x = 
	'<Units>
		<Unit id="un1">
			<UnitName>Dollars</UnitName>
			<Scale>0</Scale>
			<Symbol>$</Symbol>
			<Prefix>T</Prefix>
			<Pad>F</Pad>
			<UnitSeq>1</UnitSeq>
			<RptSeq>1</RptSeq>
		</Unit>
		<Unit id="un2">
			<UnitName>Hours</UnitName>
			<Scale>0</Scale>
			<Symbol>H</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>2</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
		<Unit id="un3">
			<UnitName>EQP</UnitName>
			<Scale>0</Scale>
			<Symbol>ep</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>3</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
		</Units>'

select 	x.value('@id','VARCHAR(32)') as rr,
    x.value('(UnitName)[1]','VARCHAR(32)') as UnitName,
	x.value('(Scale)[1]','VARCHAR(32)') scale,
	x.value('(Symbol)[1]','VARCHAR(32)') symbol,
	x.value('(Prefix)[1]','VARCHAR(32)') Prefix,
	x.value('(Pad)[1]','VARCHAR(32)') Pad,
	x.value('(UnitSeq)[1]','VARCHAR(32)') UnitSeq,
	x.value('(RptSeq)[1]','VARCHAR(32)') RptSeq

FROM @x.nodes('/Units/Unit') e(x)

Thank you so much for helping me out, I haven't done XML before in SQL. I appreciate you sticking with me.
It is perfect.

make sure to read all of the documentation and try out all of the examples. once you get your mind wrapped around the fact that all it is doing is you instructing it to read the xml like a map, it is super easy actually and superpowerful and a lot of fun!

Thanks I shall, as this is fun. 

Now on to powershell to read all the 1000's of XML in
For testing I replaced the word applicationversion with root 

The XML when I read into my new database has the heading in it.
<applicationname version="5,12,0,0">
	<Units>

How to add the first line to the front of the /Units, note it does have the quotes in it.
FROM @x.nodes('/Units/Unit') e(x)

learning = trying. what have you tried? post to show us what you have tried

DECLARE @x XML
declare @i int = 1

SET @x = 
	'<applicationname version="5,12,0,0">
<Units>
		<Unit id="un1">
			<UnitName>Dollars</UnitName>
			<Scale>0</Scale>
			<Symbol>$</Symbol>
			<Prefix>T</Prefix>
			<Pad>F</Pad>
			<UnitSeq>1</UnitSeq>
			<RptSeq>1</RptSeq>
		</Unit>
		<Unit id="un2">
			<UnitName>Hours</UnitName>
			<Scale>0</Scale>
			<Symbol>H</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>2</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
		<Unit id="un3">
			<UnitName>EQP</UnitName>
			<Scale>0</Scale>
			<Symbol>ep</Symbol>
			<Prefix>F</Prefix>
			<Pad>T</Pad>
			<UnitSeq>3</UnitSeq>
			<RptSeq>0</RptSeq>
		</Unit>
		</Units>
		</applicationname>'

select 	x.value('@id','VARCHAR(32)') as rr,
     x.value('../../@version','VARCHAR(32)') as versionn,
    x.value('(UnitName)[1]','VARCHAR(32)') as UnitName,
	x.value('(Scale)[1]','VARCHAR(32)') scale,
	x.value('(Symbol)[1]','VARCHAR(32)') symbol,
	x.value('(Prefix)[1]','VARCHAR(32)') Prefix,
	x.value('(Pad)[1]','VARCHAR(32)') Pad,
	x.value('(UnitSeq)[1]','VARCHAR(32)') UnitSeq,
	x.value('(RptSeq)[1]','VARCHAR(32)') RptSeq

FROM @x.nodes('/applicationname/Units/Unit') e(x)

how would you explain what you see? for example this

x.value('../../@version','VARCHAR(32)') as versionn,

As I am storing the contents into a database table 
    INSERT INTO XMLwithOpenXML(FileName, XMLData, LoadedDateTime)
    SELECT 'myfile.xml',CONVERT(XML, BulkColumn,2) AS BulkColumn, GETDATE() 
    FROM OPENROWSET(BULK 'c:\myfile.xml', SINGLE_BLOB) AS x;

   This allows you to see the xml file in a database
<ApplicationVersion version="6,2,0,0">
  <Units>
    <Unit id="un1">
      <UnitName>Dollars</UnitName>
      <Scale>0</Scale>
      <Symbol>$</Symbol>
      <Prefix>T</Prefix>
      <Pad>F</Pad>
      <UnitSeq>1</UnitSeq>
      <RptSeq>1</RptSeq>
    </Unit>
  </Units>
</ApplicationVersion>

I thought perhaps I could just update from
to

    Update [dbo].[XMLwithOpenXML]
    Set    XMLData = replace(XMLData, '<ApplicationVersion version="6,2,0,0">', 'root')

Got an error
Argument data type xml is invalid for argument 1 of replace function.

Then I thought I try a variable name instead
DECLARE @VERSION NVARCHAR(50)
SET @VERSION = ''
PRINT @VERSION
select
@FILENAME as FileName ,
x.value('@id','VARCHAR(10)') as un_id,
x.value('(UnitName)[1]','VARCHAR(255)') as UnitName,
x.value('(Scale)[1]','smallint') as Scale,
x.value('(Symbol)[1]','VARCHAR(5)') as Symbol,
x.value('(Prefix)[1]','CHAR(1)') as Prefix,
x.value('(Pad)[1]','VARCHAR(1)') as Pad,
x.value('(UnitSeq)[1]','smallint') as UnitSeq,
x.value('(RptSeq)[1]','smallint') as RptSeq

    FROM @x.nodes(@VERSION) e(x)

Ideally I like to just use the @variable rather than update the XML table

use the search feature in this forum